Alma has used the estimates provided by Dan to determine the revenue that could

Question

Alma has used the estimates provided by Dan to determine the revenue that could be expected from the mine. She has also projected the expense of opening the mine and the annual operating expenses. If the company opens the mine, it will cost $750 million today, and it will have a cash outflow of $115 million nine years from today in costs associated with closing the mine and reclaiming the area surrounding it. The expected cash flows each year from the mine are shown in the table that follows. Bullock has a 12 percent required return on all of its gold mines. Year Cash Flow 0 -750,000,000 1 140,000,000 2 180,000,000 3 210,000,000 4 230,000,000 5 205,000,000 6 185,000,000 7 160,000,000 8 110,000,000 9 -750,000,000 Write a VBA script that calculates the payback period for a project.

Explanation / Answer

year : cash flow : cummulative cash flow
0 : 750,000,000 :
1 : 140,000,000 : 140,000,000
2 : 180,000,000 : 320,000,000
3 : 210,000,000 : 530,000,000
4 : 230,000,000 : 760,000,000
5 : 205,000,000 : 965,000,000
6 : 185,000,000 : 1150,000,000
7 : 160,000,000 : 1310,000,000
8 : 110,000,000 : 1420,000,000
9 : 115,000,000 : 1535,000,000

steps to calculate payback period

1) We add up the cash inflows beginning after the initial cash outlay in the cumulative cash inflows column

2) We keep an eye on this last column and track the last year for which the cumulative total does not exceed the initial cash outlay

3) We compute the part or fraction of the next year's cash inflow need to payback the initial cash outlay by taking the initial cash outlay less the cumulative total in the last step then divide this amount by the next years cash inflow.
in this problem ( $750,000,000 - $530,000,000 ) / $230,000,000 = 0.9565

4) Since we said these were annual cash flows thus to obtain the payback period in years , we take the figure from the last step and add it to the year from the step 2.
Thus our payback period is 3 + 0.9565 = 3.9565 years

5) Instead of representing the years as a decimal value we could represent the payback period in years and months this way We take the fraction 0.9565 and multiply it by 12 to get the months which is 11.48 months. Thus our payback period is 3 years and 11 months.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.