VIEWS: 10 PAGES: 38 CATEGORY: Business POSTED ON: 11/18/2010
Zero Interest Credit Card Offers document sample
FV Example 1: $100 Initial Deposit Only 6% Annual Interest, 3 years, Compounds Semi-Annually. Compounds: Semi-Annually Yearly Rate Years Payments Initial Deposit (PV) Type Future Value FV Example 2: $5000 Initial Deposit & $200 per month Deposits 4% Annual Interest, 18 Years, Compounded Monthly Compounds: Monthly Yearly Rate Years Monthly Payments Initial Deposit (PV) Type Future Value FV Example 3: $500 Yearly Payments 4% Annual Interest, 10 Years, Beg. Of Period Compounds: Yearly Yearly Rate Years Payments Initial Deposit (PV) Type Future Value You are opening an account with $100. The account compounds semi-annually. At 6% yearly interest, what will you have in 3 years? You wish to open a account for you newborn’s college in 18 years. You plan on making an initial deposit of $5,000 and making monthly deposits of $200. The account compounds monthly with a yearly rate of 4%. How much will you have in 18 Years? You are depositing $5,000 into an account yearly for the next 10 years at 4% annual interest. Interest is compounded yearly at the beginning of the period. What is the Future Value? =Effect(Nominal Rate,Number of Compounding Periods Per Year) Effective Rate Example 1: Comparing Different Loan Rates We are looking for a credit card company that offers the best rate and have found three we wish to compare. The first has a nominal rate of 17% and compounds monthly, the second has a rate of 17.5% and compounds semi-annually (twice a year), and the third has a rate of 18% and compounds quarterly. Which one has the best Nominal Rate Periods Per Year Effective Rate 17.0% 12 17.5% 2 18.0% 4 Effective Rate Example 2: FV with Yearly Payments, Compounding Monthly We are making yearly payments of $1000 for 5 years into an account which compounds monthly nominal interest rate is 6%. The payments are made at the end of each year (regular annuity). The number of payments per year must equal the number of compounding periods per year. Because Payments are made yearly, we need an equivalent rate which compounds yearly (i.e. the effective Example 2: Payments & Compounding Periods Differ (5 Year Loan) Annual Rate: Number of Times Compounded per year: Life of Loan: Payments (Made Yearly): Type: Rate Equivalent Using Effect() Future Value: three we wish to compare. e of 17.5% and compounds terly. Which one has the best monthly. The annual ar annuity). s per year. s yearly (i.e. the effective =Effect(Nominal Rate,Number of Compounding Periods Per Year) =Nominal(Effective Rate,Number of Compounding Periods Per Year) We are making semi-yearly payments of $1000 into an account for 3 years. The account compounds annual nominal interest rate is 4%. The payments are made at the end of each period (regular annuity). Find the Future Value. The number of payments we are making per year must equal the number of compounding Step 1: First, we will find the Effective yearly rate equilavent of an account which compounds monthly. Step 2: Now that we have the effective yearly rate, we use nominal() to convert it into the nominal rate for an account Payments & Compounding Periods Differ (5 Year Loan) Annual Rate: Number of Times Compounded per year: Life of Loan: Payments (Semi-Yearly): Type: Effective Yrly Rate for an account compounding Monthly: Nominal Yrly Rate for an account compounding Semi-Annually: Future Value: The account compounds monthly. The ach period (regular annuity). Find the f compounding periods per year. which compounds monthly. vert it into the nominal rate for an account You are loaning an associate $1,000. For repayment options, the suggest the scenarios below. The going market rate is 4% compounded monthly. Which option should you take? Assume risk is the same for all three options. OPTION A: Will pay you $1,200 after 1 year. OPTION B: Will pay you $1,400 at the end of 2 years. OPTION C: Will pay you $100 per month for 12 months then $150. Scenario Option A Option B Option C Yearly Rate: Years: Payments: Future Value: Type: Present Value: Bond Cash Flow Discounting Example – PV() We have purchased a 3 year bond with a face value of $1,000 for $961.15. Its coupon rate is 6% resulting in yearly payments of $60 (face value x coupon rate). The market is currently paying about 5% for investments of equal risk. What is the Bond's Present Value? =PV(Rate,#periods,CouponPayment,FaceValue) Bond Cash Flow Discounting Example - PV() Face Value: Years to Maturity: Coupon Rate: Present Value (Purchase Price): Discount Rate Used for Valuation: Yearly Coupon Payments: Present Value: (Includes Coupon Payments + Face Value) s 6% resulting in yearly vestments of equal risk. What =PMT(Rate,#ofPeriods,PV,FV,Type) PMT Example 1: Loan Payments You are planning to buy a new car. You are takin Rate: 5 year loan of $20,000 to help you pay for it and Years: borrowing at an interest rate of 6%. What will t Present Value Future Value Type Monthly Payments: PMT Example 2: Retirement Goal Using Effective Rate Nominal Rate: You would like to have $1,000,000 in yo Effective Rate: account by the time you retire. You ha Years: retirement and you have found a CD ac 5.5% annual interest. You plan on mak Present Value payments into an account which comp Future Value How much do you have to deposit each Type your goal? Yearly Payments: Effect(Nominal Rate,Compounding Per ing to buy a new car. You are taking out a $20,000 to help you pay for it and you are an interest rate of 6%. What will the would like to have $1,000,000 in your savings unt by the time you retire. You have 30 years until ement and you have found a CD account which pays annual interest. You plan on making yearly ments into an account which compounds monthly. much do you have to deposit each year to reach ct(Nominal Rate,Compounding Periods) =NPER(Rate,Payments,PV,FV,Type) You are planning on making montlhly deposits of $100 into a savings account which compounds monthly with a yearly rate of 4%. How many years will it take you to have $25,000? What will the date be? (From the current date?) Rate Payments Present Value Future Value Type Number of Periods: Number of Years: Today's Date: Target Date: Rate(#periods,Payments,PresentValue,FutureValue,Type,Guess) Rate Example 1: Return on your Investment: Single Payment Present Value: Periods (In Years): Payments: Future Value: Yearly Rate if Compounded Yearly: Monthly Rate if Compounded Monthly: Yearly Rate if Compounded Monthly: Rate Example 2: Return on your Inventment - Multiple Payments You are investing in Purchase Price (PV) $50 at the end of every 6 mon Compounding Periods: payments). At the end of the Payments: payment of $209,18. Future Value: Rate Per Period: What rate are you getting for Rate Per Year: What rate are you getting per 5 Years ago you purchased a 1969 Ford Falcon for $5,000 and spent $1000 fixing it up the day you purchased it. You sold it today for $10,000. What return did you get on your investment ? Find the Yearly Rate if Compounded Yearly. Find the Montly Rate if compounded Monthly. Find the Yearly Rate if Compounded Monthly. You are investing in an annuity that requires you to deposit $50 at the end of every 6 months for 2 years (i.e. total of 4 payments). At the end of the 2 years you will receive a payment of $209,18. What rate are you getting for each period? What rate are you getting per year? NPV() - FINDING PRESENT VALUE OF AN ANNUITY WHEN CASH FLOWS VARY The PV requires that the cash flows all be of an equal amount (i.e. an annuity). This section shows how to use Excel’s NPV() series of cash flows are evenly spaced but of varying amounts. =NPV(Rate,Range of Cash Flows) Rate: This is the rate of interest over one period. Range of Cash Flows: These must be equally spaced apart in time but may vary in amount. The cash flows must be at the END of the period. The cash flows must be in the order they occur over time. The sign is important. When using NPV(), cash outflows should always be negative and inflows positive. NPV() Example 1:Initial Investment is Today – Cash Flows Vary You are considering an investment opportunity that will cost you $3,000 today and pay the following amounts at the end $700, $900, and $850. The going interest rate of investments of similar risk is 6%. Should you make the investment? NPV Example 1 - Investment Today NPV Example 2 - Investment in One Year Annual Rate: Annual Rate: Year Amount Year 1 1 2 2 3 3 4 4 5 5 6 Present Value: Net Present Value: Net Present Value: FLOWS VARY n shows how to use Excel’s NPV() function to determine the Present Value when a n amount. nflows positive. he following amounts at the end of each year for the following 5 years: $500, $600, d you make the investment? ple 2 - Investment in One Year Amount Internal Rate of Return: =IRR(Values,Guess) It is the interest rate received for an investment consisting of payments and income what occur at regular periods. More technically, the Internal Rate of return of a series of cash flows is the discount rate which sets the sum of the present value of these cash flows to zero. When comparing multiple investments, the higher the rate the better and negative rates are always bad. IRR Example 1 - Cash flows must occur at regular period Year Amount - The amount of the cash flows do 0 for an annuity. - IRR uses the order of values to interpre 1 enter your payment and income values i 2 - The initial flow is the investment and m 3 should be positive. If in one of your year 4 cannot use Excel's IRR function. A negati 5 and Excel only displays a single value for Internal Rate of Return: Note that IRR assumes cash generated d the rate calculated by IRR. This can over generate a lot of cash over the life of the at regular te which sets s, the higher must occur at regular periods. nt of the cash flows do not have to be identical as they would be he order of values to interpret the order of cash flows. Be sure to payment and income values in the sequence they occur. flow is the investment and must be negative. Subsequent flows ositive. If in one of your years you have a negative cash flow, you Excel's IRR function. A negative cash flow produces and second IRR nly displays a single value for IRR. Use MIRR instead. R assumes cash generated during the investment is reinvested at culated by IRR. This can overstate IRR for investments which ot of cash over the life of the investment. Modified Internal Rate of Return: =MIRR(Values,FinanceRate,ReinventRate) IRR assumes that the rate you finance your investment is the same as the rate you reinent your profits. Further, it cannot have any negative cash flows after the initial inventment. MIRR addresses both of these issues. You can use MIRR() if you have any future negative cash flows. MIRR Example 2: Single Investment Investment Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Finance Rate: Reinvest Rate: MIRR Bond Cash Flow Discounting Example – PV() In this example, we wish to see the profit made if our cash flows were discounted. We will use Excel’s PV() function to find their present value. Our bond is a 3 year bond with a 6% coupon rate paid annually, and a $1,000 face value which we purchased for $961.25. The market is currently paying about 5% for investments of equal risk. Find the present value of your cash inflows. =PV(Rate,#periods,CouponPayment,FaceValue) Bond Cash Flow Discounting Example - PV() Face Value: Years to Maturity: Coupon Rate: Present Value (Purchase Price): Discount Rate Used for Valuation: Yearly Coupon Payments: Present Value: (Includes Coupon Payments + Face Value) Bond Cash Flow Discounting Example – NPV() This is the same example as on the "Bonds_PV" tab, this time we will use NPV() instead. Our bond is a 3 year bond with a 6% coupon rate paid annually, and a $1,000 face value which we purchased for $961.15. The market is currently paying about 5% for investments of equal risk. Find your net discounted profit. =NPV(Rate,CashFlows) Bond Cash Flow Discounting Example - NPV() Face Value: Years to Maturity: Coupon Rate: Present Value (Purchase Price): Discount Rate Used for Valuation: Y1 Coupon Payment Y2 Coupon Payment Y3 Coupon Payment + Face Value: Net Present Value: Bond Example – Yield to Maturity using Rate() You have purchased a bond with a face value of $1,000 and coupon rate of 6% at a discount of 96-08. It is a 3 year bond and pays semi-annually. What is the Yield to Maturity? Rate(#periods,Payments,PurchasePrice,FaceValue,Type,Guess) Bond Example: Yield to Maturity Using Rate() On Date Face Value: Coupon Rate: Years to Maturity: Payments Per Year: Purchase Rate (96-08): Purchase Price: Yield to Maturity: Bond Example – Yield to Maturity using IRR() You have purchased a bond with a face value of $1,000 and coupon rate of 6% at a discount of 96-08. It is a 3 year bond and pays semi-annually. What is its Internal Rate of Return? IRR(Range) Bond Example: Yield to Maturity Using IRR() - On Date Investment: Payment 1 Payment 2 Payment 3 Payment 4 Payment 5 Payment 6 + Face Value Semi-Annual IRR: Yearly IRR: Yield(Settlement,Maturity,CouponRate,PR,Redemption,Frequency,Basis) Settlement Maturity Coupon Rate PR Redemption Frequency This is the date you This is the This is the The Market Amount paid by The number take ownership of the maturity annual coupon Price as a issuer as a non- of coupon bond. Typically 3 date written rate written non-decimal decimal percent payments per days after the trade. on the bond. on the bond. percent of of Face Value. year. Face Value. Bond Example - YTM for off Coupon Date Sales Quoted Bond Price: Bond Example YTM using Yield() Face Value: Coupon Date Maturity Date: On March 3, 2009 you purchased a bo Purchase Date (Settlement Date): $1,000 and a coupon rate of 8% paid s Previous Coupon Date: last coupon date was on 12/15/2008 a Rate: you not including accrued interest was matures on 12/15/2019. What is the Y Payment Frequency: YTM Using Yield(): ency,Basis) Frequency Basis The number Day count of coupon basis to use. payments per (See chart below.) Example YTM using Yield() – Bond Not Purchased on rch 3, 2009 you purchased a bond with a face value of and a coupon rate of 8% paid semi-annually. The bond’s upon date was on 12/15/2008 and the price quoted to t including accrued interest was $961.25. The bond es on 12/15/2019. What is the Yield to Maturity? Bond Valuation Model - Price() Required Annual Rate: =Price(SettlementDate Face Value Redemption as % of Par Settlement Annual Coupon Rate Date Payment Frequency This is the date you Issue Date purchased the Maturity Date bond. Settlement Date First Interest Payment Date Day Count Basis Basis You can calculate the numbe Price as % of Par: number of days in the year by Clean Price in Dollars: is some use 365/frequency w Accrued Interest Dirty Price: =Accrint( Calc Method (Optional) This becomes importan interest from the issue d Price(SettlementDate,MaturityDate,Rate,RequiredYield,Redemption,Frequency,Basis) Settlement Maturity Rate Required Yield Redemption Frequency Basis Date Date This is the This is the This is the # of coupon Method used to This is the This is the annual minimum bond’s payoff payments per calculate the date you date the bond interest rate desired rate of per $100 of year. number of days purchased the is due. written on the return you are face value. between bond. bond. willing to accept. Basis You can calculate the number of days between coupon payments by dividing the 0 (or omitted): US (NASD) 30/360 number of days in the year by the number of compounding periods per year. The issue 1Actual/actual is some use 365/frequency while others might use 360/frequency. The numbers to the 2Actual/360 3Actual/365 4European 30/360 =Accrint(IssueDate,FirstInterestDate,SettlementDate,Par,Frequency,Basis,CalcMethod Issue Date First Interest Settlement Par Frequency Basis Date the bond Date Date Bond’s face # of coupon See was issued. Date of the Date you value. payments per above. first interest purchased the payment. Calc Method (Optional) This becomes important when the settlement date is after the first interest date and affects how interest is calculated. Type interest from the issue date to settlement date and type 0 returns interest from the 1st payment date to settlement date. Type 1 Accrued Interest Issue 1st Interest Settlement Payment Type 0 Accrued Interest ) Basis Method used to calculate the number of days between S (NASD) 30/360 CalcMethod) Basis Calc Method See See below. above. est is calculated. Type 1 returns to settlement date. Settlement