1. Begin by writing a FUNCTION to discount cash flows and determine their NPV. a. Take the discount rate, terminal growth rate and cash flows
1. Begin by writing a FUNCTION to discount cash flows and determine their NPV.
a. Take the discount rate, terminal growth rate and cash flows as inputs. Assume 8 cash flows, from time 0 to time 7 are input by the user (so you dont need to allow for a dynamically-sized array).
b. Assume the last explicit period cash flow occurs at time 5, meaning that starting with the time 6 cash flow (the 7th value in the cash flow array), the cash flows will grow at the same rate forever. This isnt the most flexible function, but it works for the task at hand.
c. Output the NPV to the user.
2. Choose three of the incorrect valuation methods provided in the Excel file, and write a FUNCTION for each that gives the matching, incorrect answer. Follow the same steps (re-use most of the code) from part 1.
3. Write a SUB (program) that takes the same inputs as the function, but also the users answer to the problem. These inputs should be through input boxes; either enter values directly or enter cell references to the cells that contain the values. For example, if the time zero cash flow of $100 is in cell C9, you can either enter $100 or C9 into the input box. Check whether the answer is correct (to the nearest dollar). If not, test whether their answer matches one of the known errors from the three incorrect valuation functions you created (again, to the nearest dollar). Let the user know whether the answer is right or wrong, and if it is wrong, what mistake was likely made (or, if none of the incorrect answers matched, that an unknown mistake was made).
A B D E F G . T 7 $5,197.04 1 2 3 4 5 6 $3,155.73 $3,970.51 $4,949.48 $5,481.69 $5,465.37 $4,949.56 $49,496 0.870 0.756 0.658 0.572 0.497 $2,744 $3,002 $3,254 $3,134 $ 27,325 4 5 6 1 $3,156 2 $3,971 3 $4,949 7 $5,197 $5,482 $5,465 $4,950 $51,970 0.432 $24,608 0.870 $2,744 0.756 $3,002 0.658 $3,254 0.572 $3,134 0.497 $2,717 4 6 1 $3,156 2 $3,971 3 $4,949 5 $5,465 $5,482 $4,950 7 $5,197 $54,569 0.376 $22,468 0.870 $2,744 0.756 $3,002 0.658 $3,254 0.572 $3,134 0.497 $2,717 0.432 $2,140 1 3 6 1 Discount Rate 15% 2 Terminal Growth Rate 5% 3 4 Correct Valuation: 5 Year 0 6 VCFS $0.00 7 Terminal Value 8 Discount Factor 9 PV of Cash Flows 10 NPV $39,460.23 11 12 Correct Valuation (Alte mative 1): 13 Year 0 14 VCFS $0.00 15 Terminal Value 16 Discount Factor 17 PV of Cash Flows 18 NPV $39,460.23 19 20 Correct Valuation (Alte mative 2): 21 Year 0 22 VCFS $0.00 23 Terminal Value 24 Discount Factor 25 PV of Cash Flows 26 NPV $39,460.23 27 28 Incorrect (Terminal Value Not Discounted) 29 Year 0 30 VCFS $0.00 31 Terminal Value 32 Discount Factor 33 PV of Cash Flows 34 NPV $64,347.77 35 36 Incorrect (Extra Cash Flows Included) 37 Year 0 38 VCFS $0.00 39 Terminal Value 40 Discount Factor 41 PV of Cash Flows 42 NPV $43,553.82 43 44 Incorrect (Terminal value in 6th Year) 45 Year 0 46 VCFS $0.00 47 Terminal Value 48 Discount Factor 49 PV of Cash Flows 50 NPV $36,250.49 51 52 Incorrect (Cash Flows Shifted One Year Earlier) 53 Year 0 0 54 VCFs $0.00 55 Terminal Value 56 Discount Factor 57 PV of Cash Flows 58 NPV $45,379.27 59 2 $3,971 4 $5,482 7 $5,197 $3,156 $4,949 $4,950 5 $5,465 $49,496 0.497 $2,717 0.658 0.870 $2,744 0.756 $3,002 0.572 $3,134 $3,254 2 3 4 1 $3,156 6 $4,950 7 $5,197 $3,971 $4,949 $5,482 5 $5,465 $49,496 0.497 $27,325 0.658 0.870 $2,744 0.756 $3,002 0.572 $3,134 0.432 $2,140 0.376 $1,954 $3,254 2 3 4 5 1 $3,156 7 $5,197 $3,971 $4,949 $5,482 $5,465 6 $4,950 $49,496 0.432 0.658 0.376 0.870 $2,744 0.756 $3,002 0.572 $3,134 0.497 $2,717 $3,254 $21,398 1 5 6 2 $3,971 3 $4,949 4 $5,482 7 7 $5,197 $3,156 $4,950 $5,465 $49,496 0.572 $31,424 0.497 0.432 1.000 $3,156 0.870 $3,453 0.756 $3,743 0.658 $3,604 A B D E F G . T 7 $5,197.04 1 2 3 4 5 6 $3,155.73 $3,970.51 $4,949.48 $5,481.69 $5,465.37 $4,949.56 $49,496 0.870 0.756 0.658 0.572 0.497 $2,744 $3,002 $3,254 $3,134 $ 27,325 4 5 6 1 $3,156 2 $3,971 3 $4,949 7 $5,197 $5,482 $5,465 $4,950 $51,970 0.432 $24,608 0.870 $2,744 0.756 $3,002 0.658 $3,254 0.572 $3,134 0.497 $2,717 4 6 1 $3,156 2 $3,971 3 $4,949 5 $5,465 $5,482 $4,950 7 $5,197 $54,569 0.376 $22,468 0.870 $2,744 0.756 $3,002 0.658 $3,254 0.572 $3,134 0.497 $2,717 0.432 $2,140 1 3 6 1 Discount Rate 15% 2 Terminal Growth Rate 5% 3 4 Correct Valuation: 5 Year 0 6 VCFS $0.00 7 Terminal Value 8 Discount Factor 9 PV of Cash Flows 10 NPV $39,460.23 11 12 Correct Valuation (Alte mative 1): 13 Year 0 14 VCFS $0.00 15 Terminal Value 16 Discount Factor 17 PV of Cash Flows 18 NPV $39,460.23 19 20 Correct Valuation (Alte mative 2): 21 Year 0 22 VCFS $0.00 23 Terminal Value 24 Discount Factor 25 PV of Cash Flows 26 NPV $39,460.23 27 28 Incorrect (Terminal Value Not Discounted) 29 Year 0 30 VCFS $0.00 31 Terminal Value 32 Discount Factor 33 PV of Cash Flows 34 NPV $64,347.77 35 36 Incorrect (Extra Cash Flows Included) 37 Year 0 38 VCFS $0.00 39 Terminal Value 40 Discount Factor 41 PV of Cash Flows 42 NPV $43,553.82 43 44 Incorrect (Terminal value in 6th Year) 45 Year 0 46 VCFS $0.00 47 Terminal Value 48 Discount Factor 49 PV of Cash Flows 50 NPV $36,250.49 51 52 Incorrect (Cash Flows Shifted One Year Earlier) 53 Year 0 0 54 VCFs $0.00 55 Terminal Value 56 Discount Factor 57 PV of Cash Flows 58 NPV $45,379.27 59 2 $3,971 4 $5,482 7 $5,197 $3,156 $4,949 $4,950 5 $5,465 $49,496 0.497 $2,717 0.658 0.870 $2,744 0.756 $3,002 0.572 $3,134 $3,254 2 3 4 1 $3,156 6 $4,950 7 $5,197 $3,971 $4,949 $5,482 5 $5,465 $49,496 0.497 $27,325 0.658 0.870 $2,744 0.756 $3,002 0.572 $3,134 0.432 $2,140 0.376 $1,954 $3,254 2 3 4 5 1 $3,156 7 $5,197 $3,971 $4,949 $5,482 $5,465 6 $4,950 $49,496 0.432 0.658 0.376 0.870 $2,744 0.756 $3,002 0.572 $3,134 0.497 $2,717 $3,254 $21,398 1 5 6 2 $3,971 3 $4,949 4 $5,482 7 7 $5,197 $3,156 $4,950 $5,465 $49,496 0.572 $31,424 0.497 0.432 1.000 $3,156 0.870 $3,453 0.756 $3,743 0.658 $3,604
