Question

A young engineer is evaluating a 5-year investment, by looking at the cash flows at the end of each year. The capital cost of the project is considered as occurring at the end of a hypothetical year, 0. The project has no start-up revenue, but requires a start-up capital cost of $50,000. The project promises revenues of $0, $20,000; $30,000; $40,000; $50,000, and $100,000; at the end of years 0, 1, 2, 3, 4, and 5, respectively. In addition to the $50,000 start-up cost at the end of year, 0; the project has operating costs of $24,000 at the end of each year 1, 2, 3, 4, and 5.

The engineer needs to know the net present value of this investment at a given annual interest rate. This can be done by finding the present value of each yearly revenue, B_n, and each yearly Cost, C_n, at the given rate of interest, i. In this case, n, is the number of the year from 0 to 5.

The present value of a cash flow, P_n is given by the formula:

P_o = P_n (1 + i)^-n, where;

P_o is the present value of a cash flow, such as B_n or C_n

P_n is the cash flow item in year, n.

n, is the year; 0,1, 2, 3, 4, 5

i, is the annual interest rate expressed as a decimal.

1. Please construct an excel spreadsheet to display this information clearly. Be sure to show; each year n; from 0 to 5, each revenue amount, B_n, each Cost amount, C_n. Also show the calculated present value of each B_n term, the calculated present value of each C_n term, the net present value of each year’s cash flow, and at the end, the total net present value of the investment. Assume an annual interest rate of 5% (0.05). -

2. Use your spreadsheet to find the total net present value of the investment at an annual interest rate of 10%.   

Answer 1

Answer (a) and (b):

Calculations as required for both requirement a and b are given below:

Above excel with 'show formula' is as given below: