Question

PRESENT VALUE OF AN ANNUITY -

(a) What is the present value of an asset that pays $10,000 per year at the end of the next four years if the appropriate discount rate is 5 percent? What total return would you earn if you bought this asset and it paid its expected cash flows on time each year for the next four years? Prove that you earned the same return that you would have, had you put your money in the bank for four years at 5 percent per year? (PLEASE INCLUDE FORMULAS USED TO SOLVE PROBLEM IN EXCEL).

(b) What is the present value of an asset that pays $15,000 per year at the end of the next five years if the appropriate discount rate is 6 percent? What total return would you earn if you bought this asset and it paid its expected cash flows on time ecah year for the next five years? Prove that you earned the same return that you would have, had you put your money in the bank for five years at 6 percent per year. (PLEASE INCLUDE FORMULAS USED TO SOLVE PROBLEM IN EXCEL).

NET PRESENT VALUE OF A PROJECT -

(c) What is the net present value of a project that has upfront costs of $5 million and pays end of the year cash flows of $1 million in one year, $2 million in two years, and $3 million in three years if the annual discount rate for the project is 3 percent? Show how much money you would have at the end of three years if you bought the project and what you would have instead if you banked your $5 million for three years at 3 percent. (PLEASE INCLUDE FORMULAS USED TO SOLVE PROBLEM IN EXCEL).

(d) What is the net present value of a project that has upfront costs of $6.8 million and pays end of the year cash flows of $1.5 million in one year, $2.5 million in two years, and $3.5 million in three years if the annual discount rate for the project is 4 percent? Show how much money you would have at the end of three years if you bought the project and what you would have instead if you banked your $6.8 million for three years at 4 percent. (PLEASE INCLUDE FORMULAS USED TO SOLVE PROBLEM IN EXCEL)

Answer 1

a) Present Value can be calculated using PV function in excel

PV = PV(rate = 5%, nper = 4, pmt = -10,000, fv = 0, 0) = $35,459.51

In order to calculate total return earned, we need to calculate the future value using FV function

FV = FV(rate = 5%, nper = 4, pmt = -10,000, fv = 0, 0) = $43,101.25

Total Return = (43,101.25 / 35,459.51) - 1 = 21.55%

If you put $35,459.51 in bank account at 5%, value of account after 4 years = 35,459.51 x (1 + 5%)^4 = $43,101.25

Hence, you would earn the same total return.

b) PV(rate = 6%, nper = 5, pmt = -15,000, fv = 0, 0) = $63,185.46

FV(rate = 6%, nper = 5, pmt = -15,000, pv = 0, 0) = $84,556.39 will be the future value of the asset.

=> Total Return = 84,556.39 / 63,185.46 - 1 = 33.82%

If you had $1 in bank, the future value of account = 1 x (1 + 6%)^5 = 1.3382

=> Your total return here as well = 33.82%

c) Net present value can be calculated using NPV function in excel.

Insert -5, 1, 2, 3 in an excel column, then NPV = NPV(rate = 3%, values = 1,2,3) - 5 = $0.60 million

PV of the cash flows = 0.6 + 5 = $5.6 million and future value of asset = FV(rate = 3%, nper = 3, pmt = 0, pv = -5,6, 0) = $6.12 million

If you put $5m in bank account, you would have FV(rate = 3%, nper = 3, pmt = 0, pv = -5, 0) = $5.46 million

d) NPV = NPV(rate = 4%, values = 1.5,2.5,3.5) - 6.8 = $0.07 million

In this project, future value = FV(rate = 4%, nper = 3, pmt = 0, pv = -6.87, 0) = $7.72 million

In bank, future value = FV(rate = 4%, nper = 3, pmt = 0, pv = -6.87, 0) = $7.65 million