MBAC 6020 p. 5

USING EXCEL FOR TIME VALUE OF MONEY CALCULATIONS

Once 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 rate
Nper: / the number of periods
Pmt: / 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: / 6
Pmt:
Fv: / 15000
Type:
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 annuity

Example 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%/12
Nper: / 6*12
Pmt:
Fv: / 15000
Type:
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%/12
Nper: / 20
Pmt: / 2000
Fv:
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%/12
Nper: / 20
Pmt: / 2000
Fv:
Type: / 1
Answer / ($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%/2
Nper: / 20*2
Pmt: / 10000*6%*1/2
Fv: / 10000
Type:
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 rate
Nper: / the number of periods
Pmt: / 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%/12
Nper: / 6*12
Pmt:
Pv: / -15000 (the sign is minus because she is paying the bank)
Type:
Answer / $20,235.27

Example 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%/12
Nper: / 20
Pmt: / -2000
Pv:
Type:
Answer / $41,623.63

Example 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%/12
Nper: / 20
Pmt: / -2000
Pv:
Type: / 1
Answer / $41,797.06

Example 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%/12
Nper: / 20
Pmt: / -2000
Pv: / -10000
Type:
Answer / $52,490.79


PMT (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?

Rate / 8%/12
Nper / 12*4
Pv / -70000
Fv / 8000
Type
Answer / $1,566.93

Note: 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):

Rate / 8%/12
Nper / 12*4
Pv / -70000
Fv / 8000
Type / 1
Answer / $1,556.56

RATE (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.

Nper / 3
Pmt / 600
Pv / -10000
Fv / 10100-600
Type
Answer / 4.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.

Rate / 8%
Value1 / -8000 (payments you make are negative)
Value2 / -1000
Value3 / 2000
Value4 / 3000
Value5 / 3000
Value6 / 3000
Value7 / 3000
Answer / $1,210.74

Note: if the answer, above, had been negative then the return on the investment would not cover your cost of capital

As 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.