PV Tutorial Using Excel

(1)

E Y K 1 5 - 3

PV Tutorial Using Excel

INTRODUCTION

Excel is a spreadsheet application that includes tools to make the solving of financial equations efficient. This tutorial will focus on Excel’s ability to solve PV/FV problems.

It will be based on the same Exercises used in EYK 15-2 where PV/FV problems were solved using calculator keystrokes in lieu of PV/FV tables (the tables are in EYK 15-1).

(2)

When you open an Excel spreadsheet (worksheet), it will look like the following:

Functions to solve PV/FV problems can be accessed by clicking on the arrow next to the ‘Σ’ icon. A dropdown menu as shown will appear from which you can make the appropriate function choice to help you solve a problem.

Click on ‘More Functions’ in the dropdown menu and the following applet will appear:

To solve the first exercise in this tutorial, we will need to type in FV because we are trying to calculate the future value.

(3)

Then click ‘OK’ at the bottom of the applet and the following screen will appear.

Notice that it tells you what you are trying to solve for, which in this case is the FV (future value).

Let’s begin now with Exercise 1.

(4)

EXERCISE 1: CALCULATING FV (LUMP SUM)

If you invest $1,000,000 for 10 years at a stated annual rate of 6% with interest earned monthly, what is the future value?

Solution:

Rate: The interest ‘rate’ is expressed as a decimal: 6% ⫽ 0.06. We want monthly interest so we need to divide by 12.

Nper: The ‘number of periods’

is 10 years ⫻ 12 months/year or 120 periods.

Pmt: This is not an annuity question so there is no payment.

Pv: The ‘present value’ is the cash outflow, expressed as a negative (as opposed to a cash inflow which is a positive).

Type: The cash outflow (or inflow) is at the beginning of the period which is denoted with a ‘1’ (as opposed to an outflow—or inflow—at the end of a period which would be denoted by a ‘0’ or just left blank).

Formula result ⫽ $1,819,396.73, the future value of investing

$1,000,000 at the beginning of a 10-year period with interest calculated monthly at the rate of 6%.

(5)

EXERCISE 2: CALCULATING INTEREST RATE (LUMP SUM)

If you invest $1,000,000 for 10 years with interest earned monthly, what interest rate is necessary to achieve a future value of $1,938,621?

Solution: We are trying to find the interest ‘rate’ so that is the Excel function we need to look for. Use the Insert Function applet described previously to bring up the following ‘Rate’ function.

is 10 years ⫻ 12 months/year or 120 periods.

Pmt: This is not an annuity question so there is no payment.

Pv: The ‘present value’ is the cash outﬂ ow of 1000000, expressed as a negative (as opposed to a cash inﬂ ow, which is a positive).

Fv: The ‘future value’ is the required cash inﬂ ow of 1938621, a positive

(as opposed to a cash outﬂ ow, which would be a negative).

Type: The cash outfl ow (or infl ow) is at the beginning of the period, which is denoted with a ‘1’ (as opposed to an outfl ow—or infl ow—at the end of a period, which would be denoted by a ‘0’ or just left blank).

Formula result ⫽ 0.005531718 interest per period which is per month in this case ⫻ 12 months/year ⫽ 0.066381 ⫻ 100% ⫽ 6.638062%. Therefore, if we invest $1,000,000 that earns an interest rate of 6.64%

(rounded) compounded monthly, we will have

$1,938,621 at the end of 10 years.

(6)

EXERCISE 3: CALCULATING INVESTMENT PERIOD (LUMP SUM)

If you are able to invest $5,000,000 and earn interest of 10% per year, in how many years will the investment have a future value of $12,500,000?

Solution: We are trying to find the ‘number of periods’ so that is the Excel function we need to look for. Use the Insert Function applet to bring up the following ‘NPER’

function.

Rate: The interest ‘rate’ is expressed as a decimal:

10% ⫽ 0.1.

Pv: The ‘present value’ is the cash outﬂ ow of 5000000, expressed as a negative (as opposed to a cash inﬂ ow, which is a positive).

Fv: The ‘future value’ is the required cash inﬂ ow of 12500000, a positive (as opposed to a cash outﬂ ow, which would be a negative).

Type: The cash outfl ow (or infl ow) is at the beginning of the period, which is denoted with a ‘1’ (as opposed to an outfl ow—or infl ow—at the end of a period, which would be denoted by a ‘0’ or just left blank).

Formula result ⫽ 9.613776133 periods, which in this case is years. Be sure to check whether your answer is in months or years … if the interest ‘rate’ was input as a monthly rate, then the periods would be number of months. So, in this question, if we invest $5,000,000 at the rate of 10% interest, we will have $12,500,000 after 9.614 years (rounded).

(7)

EXERCISE 4: CALCULATING PV (LUMP SUM)

You want to have $150,000 in 15 years to pay for your child’s post-secondary educa- tion. Assuming interest is earned annually at a rate of 4.5%, how much do you have to invest today?

Solution: We are trying to find the ‘present value’ so that is the Excel function we need to look for. Use the Insert Function applet to bring up the following ‘PV’

function.

Rate: The interest ‘rate’ is expressed as a decimal:

4.5% ⫽ 0.045

is 15 years.

Type: Because we are

calculating present value, the formula automatically assumes that the transaction occurs at the beginning of the period.

Therefore, you get the same answer whether you leave

‘Type’ blank or include ‘1’ … both are shown to verify this for you … try it!

Formula result ⫽ ⫺77508.06635 which means that if we invest

$77,508 today, we will have

$150,000 in 15 years if the annual interest rate is 4.5%.

OR

(8)

EXERCISE 5: CALCULATING MONTHLY PAYMENTS I (ANNUITY)

You want to have $80,000 in 20 years to pay for your child’s post-secondary educa- tion. Assuming interest is earned monthly at an annual stated rate of 7.25%, what will the monthly payments be?

Solution: We are trying to find the monthly ‘payment’ so that is the Excel function we need to look for. Use the Insert Function applet to bring up the following ‘PMT’

function.

Incorrect answer Incorrect

Rate: The interest ‘rate’ is expressed as a decimal: 7.25%

⫽ 0.0725/12 (divide by 12 because payments and therefore interest additions are monthly).

is 20 years ⫻ 12 months/year.

Pv: There is no present value (no lump sum at the beginning) because this is an annuity.

Type: Payments are assumed to be made at the end of the period unless stated otherwise.

Therefore, in this case we can leave ‘Type’ blank or put in ‘0’.

If we insert a ‘1’, we get the incorrect answer even though it appears to be close to the correct answer. Be careful not to make this kind of error.

Formula result ⫽ ⫺148.9674546 which means that by paying

$148.97 (rounded) each month for 20 years, we will have

$80,000 if the annual interest rate is 7.25%.

(9)

EXERCISE 6: CALCULATING MONTHLY PAYMENTS II (ANNUITY)

You have purchased a home and are applying for a $250,000 mortgage. The stated annual interest rate is 4.8%. What will the monthly payments be if you choose to pay the house off in (a) 15 years? (b) 20 years?

Solution: We are trying to find the monthly ‘payment’ so that is the Excel function we need to look for. Use the Insert Function applet to bring up the following ‘PMT’

function.

a.

b.

(10)

EXERCISE 7: CALCULATING FV (ANNUITY)

You are starting a retirement savings plan and can afford to invest $500 at the end of each month for the next 25 years. What is the future value of this investment stream assuming a stated annual interest rate of (a) 5%? (b) 8%?

Solution: We are trying to find the ‘future value’ so that is the Excel function we need to look for. Use the Insert Function applet to bring up the following ‘FV’

function.

a.

b.

(11)

EXERCISE 8: CALCULATING PV (ANNUITY)

You want to have $2,000,000 in 22 years for your retirement. Assuming a stated annual interest rate of 7%, how much do you have to invest monthly (interest is earned monthly)?

Solution: We are trying to find the monthly ‘payment’ (how much do we invest or pay each month) so that is the Excel function we need to look for. Use the Insert Function applet to bring up the following ‘PMT’ function.

(12)

EXERCISE 9: CALCULATING INTEREST (ANNUITY)

You want to have $55,000 in 10 years to pay for a new car. You have committed to saving $300 per month. What stated annual interest rate will allow you to meet your goal? (Assume interest is earned monthly.)

Solution: We are trying to find the interest ‘rate’ so that is the Excel function we need to look for. Use the Insert Function applet to bring up the following ‘RATE’

function.

(13)

EXERCISE 10: CALCULATING INVESTMENT PERIOD (ANNUITY)

You estimate that you will need $25,000 to take your family on a memorable vaca- tion. Assume a stated annual interest rate of 6% and monthly contributions of $700 to a savings account. How many months will it take to achieve your goal?

Solution: We are trying to find the ‘number of periods’ so that is the Excel function we need to look for. Use the Insert Function applet to bring up the following ‘NPER’

function.

(14)

EXERCISE 11: CALCULATING NPV (UNEQUAL PAYMENTS)

You are purchasing a machine at a cost of $10,000 and an additional $1,500 for installation costs. You estimate the net revenues generated by the machine in years 1 through 5 will be $6,000, $14,000, $15,000, $17,000, and $18,000 respectively. After the ﬁ fth year, you will sell the machine for $2,000. What is the net present value, assuming a proﬁ t objective of 20% per year?

Solution: We are trying to find the ‘net present value’ for ‘x’ number of cash flows so that is the Excel function we need to look for. Use the Insert Function applet to bring up the following ‘XNPV’ function.

To start, set up a schedule in Excel. In Column A, as shown below, list the cash infl ows/outfl ows. In Cell A2, notice that the $10,000 purchase price of the machine was added to the $1,500 installation costs to get an $11,500 cash outfl ow (negative value because it is an outfl ow as opposed to an infl ow).

Then, in Column B, type in random dates that reﬂ ect the stream of inﬂ ows/

outﬂ ows. To get the dates to appear as shown to the left, go to the top menu bar, click on ‘format’, ‘cell’, ‘date’, and ‘*January 1, 2012’ … just makes it easier to read in the author’s humble opinion but any date format will work! Notice in Cell B7 that the value is $20,000, which is the $18,000 cash inﬂ ow from the net revenues plus the $2,000 resulting from the sale of the machine at the end of its life. NOTE: Place your cursor in ANY Excel cell other than those in the table to the left.

A B

1 Values Dates 2 ⫺11500 January 1, 2012 3 6000 December 31, 2012 4 14000 December 31, 2013 5 15000 December 31, 2014 6 17000 December 31, 2015 7 20000 December 31, 2016

Now open the ‘XNPV’ function.

Rate: 20% which is input as 0.2

Values: Put your cursor in the

‘Values’ box and then go to the Excel spreadsheet and select cells A2 to A7. You should automatically see A2:A7 appear as shown in the ‘Values’ box to the right. Now hit the tab key or manually place your cursor in the ‘Dates’ box.

Dates: With your cursor in the

‘Dates’ box, go to the Excel spreadsheet and select cells B2 to B7.

Formula result: You should now see 28145.98744. Because the result is positive, it means that the investor’s desired 20%

return will be achieved by this investment.

(15)

EXERCISE 12: CALCULATING PV (UNEQUAL PAYMENTS)

You are planning to provide customers a new service with anticipated net revenues each year for the next four years of $8,000, $15,000, $25,000, and $40,000 respectively. No initial investment is required because the service can be provided using existing unused capacity. Assume a stated annual interest rate of 8%. What is the present value of this stream of cash ﬂ ows?

Solution: We are trying to find the ‘net present value’ for ‘x’ number of cash flows so that is the Excel function we need to look for. Use the Insert Function applet to bring up the following ‘XNPV’ function.

To start, set up a schedule in Excel. In Column A, as shown below, list the cash infl ows/outfl ows. In Cell A2, notice that because there is no beginning of period cash infl ow/outfl ow we have ‘0’ at January 1, 2012. The function needs a starting point in order to properly discount the $8,000 at December 31, 2012. NOTE: Place your cursor in ANY Excel cell other than those in the table to the right.

A B

1 Values Dates 2 0 January 1, 2012 3 8000 December 31, 2012 4 15000 December 31, 2013 5 25000 December 31, 2014 6 40000 December 31, 2015

Now open the ‘XNPV’ function.

Rate: 8% which is input as 0.08

Values: Put your cursor in the

‘Values’ box and then go to the Excel spreadsheet and select cells A2 to A6. You should automatically see A2:A6 appear as shown in the ‘Values’ box to the left. Now hit the tab key or manually place your cursor in the ‘Dates’ box.

Dates: With your cursor in the

‘Dates’ box, go to the Excel spreadsheet and select cells B2 to B6.

Formula result: You should now see 69518.76374. Because the result is positive, it means that the investor’s desired 8%

return will be achieved by this investment.

PV Tutorial Using Excel

E Y K 1 5 - 3