This Excel exercise is designed to find the present value of future cash flows. Assume there are two projects X & Y with cash flows at the end of the next three years as follows.

Assume that the required rate of return on projects is equal to $10\%.$ Required Return $=10\%$

Today Year $1$ Year $2$ Year $3$

Project X $-100106070$

Project Y $-100804020$

In the space below calculate the Net Present Values of projects X & Y by using the present value formula of Excel $(=$PV$($rate$,$nper,pmt$,[$fv$]).$

Note: because these are uneven cash flow streams the pmt $=0.$ We can then solve for the PV of each cash flow, where each cash flow is a FV$.$

In cell C$14,$ you can type $"=$c$13".$ In cell D$14$ you will type $"=$PV$($$J$$2,$ D$12,0,-$D$13)".$ You can then copy cell D$14$ to cells E$14$ and F$14.$

Next you simply need to sum the cells C$14,$ C$14,$ E$14,$ and F$14,$ to calculate the NPV for Project X$.$

Project X $0123$

Cash Flows $-100106070$

PV$($CF$)$

NPV

Now we will find the NPV of Project X using a different approach. In cell C$21$ type $"=$C$20".$ In cell D$21$ type $"=$D$20/(1+$$J$$2)^$D$19".$ Copy cell D$21$ to E$21$ and F$21.$

Sum row $21$ to calculate NPV$.$

Project X $0123$

Cash Flows $-100106070$

PV$($CF$)$

NPV

Now copy cells B$12$ to F$15$ into the space below and change the information to calculate the NPV of project Y$.$ Check your answers with the book Example $2$ of Chapter $10.$

Complete the information in the Table below by changing the required rate of return, r$,$ in cell J$2$ and inputing the NPVs for projects X & Y using your analysis above.

r NPV$($X$)$ NPV$($Y$)$

$0\%$

$5\%$

$10\%$

$15\%$

$20\%$

$25\%$

Create a NPV profile in the space below, by inserting a line graph from using data in the above Table.