1) Payback period = 4 + 66000000/210000000 = 4.31 Years NPV = $ 28,451,509.51 IR
ID: 2644891 • Letter: 1
Question
1)
Payback period = 4 + 66000000/210000000 = 4.31 Years
NPV = $ 28,451,509.51
IRR = 13.25%
MIRR = 12.51%
12.51%
Question is:Most spreadsheets do not have a built-in formula to calculate the payback period. Write a VBA script that calculates the payback period for a project.
Year Cash Flow Cumualtive Cash Flow Present Value 0 (650,000,000) (650,000,000) (650,000,000.00) 1 80,000,000 (570,000,000) 71,428,571.43 2 121,000,000 (449,000,000) 96,460,459.18 3 162,000,000 (287,000,000) 115,308,400.15 4 221,000,000 (66,000,000) 140,449,495.33 5 210,000,000 144,000,000 119,159,639.70 6 154,000,000 298,000,000 78,021,192.66 7 108,000,000 406,000,000 48,853,715.26 8 86,000,000 492,000,000 34,733,957.61 9 (72,000,000) 420,000,000 (25,963,921.80) NPV = 28,451,509.51 IRR = 13.25% MIRR =12.51%
Question is:Most spreadsheets do not have a built-in formula to calculate the payback period. Write a VBA script that calculates the payback period for a project.
Explanation / Answer
Year Cash Flow Cumualtive Cash Flow Present Value 0 (650,000,000) (650,000,000) (650,000,000.00) 1 80,000,000 (570,000,000) 71,428,571.43 2 121,000,000 (449,000,000) 96,460,459.18 3 162,000,000 (287,000,000) 115,308,400.15 4 221,000,000 (66,000,000) 140,449,495.33 5 210,000,000 144,000,000 119,159,639.70 6 154,000,000 298,000,000 78,021,192.66 7 108,000,000 406,000,000 48,853,715.26 8 86,000,000 492,000,000 34,733,957.61 9 (72,000,000) 420,000,000 (25,963,921.80) NPV = 28,451,509.51 IRR = 13.25% MIRR = 12.51% Payback period = 4.31 Year Cash Flow Cumualtive Cash Flow Present Value 0 (650,000,000) =B18 =B18/1.12^A18 1 80,000,000 =C18+B19 =B19/1.12^A19 2 121,000,000 =C19+B20 =B20/1.12^A20 3 162,000,000 =C20+B21 =B21/1.12^A21 4 221,000,000 =C21+B22 =B22/1.12^A22 5 210,000,000 =C22+B23 =B23/1.12^A23 6 154,000,000 =C23+B24 =B24/1.12^A24 7 108,000,000 =C24+B25 =B25/1.12^A25 8 86,000,000 =C25+B26 =B26/1.12^A26 9 (72,000,000) =C26+B27 =B27/1.12^A27 NPV = =SUM(D18:D27) IRR = =IRR(B18:B27) MIRR = =MIRR(B18:B27,12%,12%) Payback period = =-C22/B23 +A22
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.