Using Excel For Time Value Of Money C Alculations

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:

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:

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?

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.

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.

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?

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:

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:

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?

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?

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?

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?

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

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.

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.