Question

You need to create an excel spreadsheet that answers the following questions. Where stated, make sure you solve the problem “by hand”, i.e. discounting each cash flow, and also using TVM formulas from Excel. All work should be presented in three sheets/tabs (not files). Your sheets should be labeled. Questions 1-4 are in the first tab, questions 5-6 are in the second tab, and question 7 is in a third tab. In all cases, presentation matters!!! Make these professional. Also, all work should be set up in such a way that you could change one parameter and have the formula automatically update, i.e. you need an assumptions section. In other words, you would want to be able to do a basic type of “what-if” analysis. Hard-coding values is bad, formulas are good.

1. Find the FV of $10,000 33 years in the future if the opportunity cost rate is 8% (use Excel formulas and basic PV/FV techniques).

2. Find the PV of $1,000,000 to be delivered in 16 years if the opportunity cost rate is 7% (use Excel formulas and basic PV/FV techniques).

3. Would you prefer the following (you can use just formulas for this problem):

1. An ordinary annuity where you receive $500 each year for 20 years if the opportunity cost is 6%
2. An annuity due where you receive $480 each year for 20 years if the opportunity cost rate is 5.9%

4. What is the net present value of the following stream of cash flows assuming the opportunity cost rate is 12%? Solve NPV using each cash flow and the NPV function in Excel (use Excel formulas and basic PV/FV techniques).

Year 0:             -$10,000

Year 1:            $4,000

Year 2:            -$2,000

Year 3:            $5,000

Year 4:            $3,000

Year 5:            $6,000

Answer 1

FV of $10,000, 33 years in the future if the opportunity cost rate is 8%.

This can be calculated in Excel using the FV formula as shown below:

FV(rate = 8%, nper= 33 years, ,pv = -10,000)

(Assuming annual compounding)

Present Value	$10,000
Interest rate	8%
Tenure (Years)	33
Future Value	$126,760.50

The formula is used as shown below:

PV of $1,000,000 to be delivered in 16 years if the opportunity cost rate is 7% :

PV formula in excel is used for calculation as shown below:

PV(rate = 7%, nper = 16, ,fv= -1000000)

Future Value	$1,000,000
Interest rate	7%
Tenure (Years)	16
Present Value	$338,734.60

The Excel formula is used as shown below:

An ordinary annuity where you receive $500 each year for 20 years if the opportunity cost is 6%

The formula to find the PV of an ordinary annuity is given by:

where PMT is the annuity amount = 500

r is the Interest rate = 6% = 6% / 12 per month = 0.005

n is the Tenure = 20 years = 20 * 12 = 240 months

Hence,

Hence, the Present Value of this annuity is 69,790.39

An annuity due where you receive $480 each year for 20 years if the opportunity cost rate is 5.9%

The formula to find the present value of an annuity due is given by:

where PMT is the Annuity Value = $ 480,

n is the Tenure = 20 years = 20 * 12 = 240 months

r is the rate of interest = 5.9% per annum = 5.9%/12 = 0.004917 per month

Hence, the Present Value of the Annuity Due is $ 67,873.60

Net present value of the following stream of cash flows assuming the opportunity cost rate is 12%

Given:

Year	Cashflow
Year 0:	(10,000)
Year 1 :	4,000
Year 2:	(2,000)
Year 3:	5,000
Year 4:	3,000
Year 5:	6,000

NPV calculations using individual Cashflows present values and NPV formula are shown below:

The formulas used in the above Excel are shown below:

Note: The NPV formula in Excel considers the first value as Cashflow of Year 1 and hence Year 0 Cashflow is excluded in the formula

Hence, the NPV Value is $ 847.06