USING EXCEL FOR PRESENT VALUE CALCULATIONS



USING EXCEL FOR TIME VALUE OF MONEY CALCULATIONSOnce you get familiar with these functions, you will be able to directly type in the information, but to start, it is easier to let EXCEL lead you lead you by the hand.To use these functions, click on “insert”, “function”, then choose the category “financial”.PV (Present Value Function)EXCEL’s PV function computes the present value of a lump sum payment and/or an annuity. The function has the form:=PV (discount rate, number of periods, payment, future value, type)The Pv function asks you to insert values in cells for:Rate: which is the discount rateNper: the number of periodsPmt: the amount of one payment in the annuity (if there is an annuity)Fv: the lump sum of money to be received/paid in the future (if there is one)Type:type is important only if the present value involves an annuity; type = 0 if the first payment in the annuity is one period from today and 1 if the first payment in the annuity is made today.Example 1a: A wealthy relative wants to put some money in a savings account for you today. She wants you to receive $15,000 from the account in 6 years; the bank promises her it will pay interest of 5% compounded annually on the money she deposits. How much should she deposit? You would enter:Rate: 5%Nper: 6Pmt: Fv: 15000Type:Answer($11,193.23) the answer is negative to indicate that she would have to pay that amount of money to the bank.Note:Pmt and Type are left blank, since this isn’t an annuityExample 1b: Another wealthy relative wants to put some money in a savings account for you today. She wants you to receive $15,000 from the account in 6 years; the bank promises her it will pay interest of 5% compounded monthly on the money deposited today. How much should she deposit?Rate: 5%/12Nper: 6*12Pmt: Fv: 15000Type:Answer($11,119.20)Example 2a: A third rich relative of yours wants to deposit some money in the bank in Example 1b; however, he wants you to be able to withdraw $2,000 a month from the bank every month for the next 20 months and have a zero balance in the account after the 20th month. He wants to deposit the money today and wants the bank to start giving you your checks one month from today.Rate: 5%/12Nper: 20Pmt: 2000Fv: Type:0 Note: the default for Type is 0, so we could have left Type blank.Answer($38,302.22)Example 2b: The same rich relative of yours in Example 2a changes his mind, he wants the bank to start giving you your checks starting the day he makes his deposit.Rate: 5%/12Nper: 20Pmt: 2000Fv: Type:1Answer($38,461.81)Example 3: (Chapter 9 material) X Corporation wants to raise some capital. They offer you a bond which has a face value of $10,000, a stated annual interest rate (or coupon rate) of 6% (paid twice a year starting 6 months from now), and a maturity date 20 years from today. You want to earn a return of 8% compounded semiannually. How much would you loan X Corporation?Rate: 8%/2Nper: 20*2Pmt: 10000*6%*1/2Fv: 10000Type:Answer($8,020.72)FV (Future Value Function)The function has the form:=FV (discount rate, number of periods, payment, present value, type)The Fv function asks you to insert values in cells for:Rate: the discount rateNper: the number of periodsPmt: the amount of one payment in the annuity (if there is an annuity)Pv: the lump sum of money to be received/paid today (if there is one)Type:type = 0 if you want to know how much money there will be immediately after the last payment; type = 1 if you want to know how much money there will be one period after the last payment.Example 4: A wealthy relative wants to deposit $15,000 in a savings account in the bank in Example 1b today for you. She wants to leave the money in the bank for 6 years The bank promises her it will pay interest of 5% compounded monthly. How much will be in the account in 6 years when she instructs the bank to send you a check for the balance? You would enter:Rate: 5%/12Nper: 6*12Pmt: Pv: -15000 (the sign is minus because she is paying the bank)Type:Answer$20,235.27Example 5a: Another rich relative of yours wants to deposit some money in the bank in Example 1b; he wants to deposit $2,000 a month in the bank every month for the next 20 months and immediately after making the 20th deposit, he wants to give the account to you. How much will be in the account when your get it?Rate: 5%/12Nper: 20Pmt: -2000Pv: Type:Answer$41,623.63Example 5b: The rich relative of yours from 5a changes his mind, he now wants the bank to give you the money 1 month after making the 20th deposit. How much will be in the account when your get it?Rate: 5%/12Nper: 20Pmt: -2000Pv: Type:1Answer$41,797.06Example 6: You deposit $10,000 in the bank today and starting one month from today, you deposit $2,000 every month for 20 months. The bank pays 5% interest compounded monthly. How much will be in your bank account immediately after the last payment?Rate: 5%/12Nper: 20Pmt: -2000Pv: -10000 Type:Answer$52,490.79PMT (Payment Function)Example 7a. (Chapter 10 material) You lease a machine to a customer. The machine has a fair value of $70,000; the lease period is for 4 years and the lease contains a guaranteed residual value of $8,000. You charge the lessee 8% compounded monthly. The first month’s lease payment is due one month from today. How much should each lease payment be?Rate8%/12Nper12*4Pv-70000 Fv8000TypeAnswer$1,566.93Note: Pv is entered as negative; Fv and the answer are positive since you will be receiving the Fv and the lease payments.Example 7b. (Chapter 10 material) You lease the same asset as in Example 7a under the same terms, except you want to receive the first lease payment the day the lease is signed (which is more common):Rate8%/12Nper12*4Pv-70000 Fv8000Type1Answer$1,556.56RATE (Implicit Interest Rate Function)Example 8. You want to buy an asset; the seller tells you the price is $10,000. You ask if you could get the asset today, but pay for it in the future. The seller says “Ok, if you pay me in the future, I want you to pay me $600 in one year, $600 in two years and $10,100 in three years.” You want to determine what interest rate the seller is charging you.Nper3Pmt600 Pv-10000 Fv10100-600TypeAnswer4.40%NPV (Net Present Value)The function has the form:= (NPV, Value1, Value2,…)Example 9 Your cost of capital is 8%. An opportunity arises to invest in something that will cost you $8,000 one year from today and $1,000 in one year later; you think that the investment will repay you $2,000 at the end of three years and $3,000 at the end of years four through seven. You should invest in the project if the present value of what you will receive will exceed the present value of what you must pay – that is, if the net present value is positive.Rate8%Value1-8000 (payments you make are negative)Value2-1000Value32000Value43000Value53000Value63000Value73000Answer$1,210.74Note: if the answer, above, had been negative then the return on the investment would not cover your cost of capitalAs you can see in the financial category, EXCEL has many other financial functions you can use, most of which you should now be able to figure out on your own. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download