Problem 1. A) Determine the MARR for a company that can invest excess funds at 6%...

Transcribed Image Text:

Problem 1. A) Determine the MARR for a company that can invest excess funds at 6% and requires 7% profit margin. B) What if, instead, the company borrows funds at 9%? a) MARR 13.00% b) MARR 16.00% 4 5 6 7 8 9 10 11 12 13 14 1 2 Problem 2. Consider the following series of cash flows: 3 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 32 33 A 34 35 36 37 B Example → C —— 0 b) NPV c) D -700 n = 20 IA = 0.15 İM = 1.171% Month Cumulative Undiscounted CF = 400 Discount (or PW) Factors = Discounted CFs = NPV by summing DCFs = $248.89 = Example 1 E 1200 2 F 600 Cumulative Month Amount CF (1,000s of $) $400.00 What is the NPV if the MARR yields 15%? Compute your solution by two (2) methods as follows: Example. Compute the PV month-by-month and sum those monthly results to find the NPV. I've done this for you as an example. A. Read through the example provided in the "Example" tab. Explicitly state that you reviewed the example and understand what is happening. B. Then, separately, use Excel's NPV function. 3 G 300 4 H -1000 5 I -1200 6 J -400 7 -300 K months % per year = MARRAnnual - This is an effective annual interest rate. % per month = MARR Monthly This is an effective monthly interest rate. 0 -700 1 1200 2 600 3 300 4 5 6 7 -1000 -1200 -400 -300 1 0.988426 0.976985042 0.965677 0.9545 0.943452 0.932532 0.921738 -700 1186.111 586.1910253 289.7031 -954.5 -1132.14 -373.013 -276.522 8 The example was reviewed and understood. The period contracted for the loan is 20 months with an annual interest rate of 15.00% and an effective MARR monthly interest rate at 1.171%. Undiscounted CF does not take into consideration the value of time. So for example, for year 0 the value is $700.00. With a discount factor of 1, the discounted CF would remain the same. With respect to year 1, if you 30 receive $1200.00 1 year from now, undiscounted today would be $1200 dollars, but since inflation/other factors occurs, that 1200 dollars 1 31 year later will not be $1200 dollars today. It will be a little less than $1200. With a discounted factor of 0.988421, the $1200 will be worth $1186.105. This applies to all the other years till year 20. -1000 L 9 M 1200 10 400 N 11 10 400 300 O C. Now recompute the NPV at a MARR of 65%. I know that is awfully high, but see what happens. Comment about the difference. D. Compute the FV at the end of month 20 at a MARR of 15%. Note- I do not know of an Excel function that performs this calculation. 12 11 300 1000 P 12 1000 13 -1200 Q 14 -400 R 14 -400 15 -300 S 15 -300 16 1000 T 16 1000 17 1200 U 17 1200 18 19 20 13 -1200 300 -1000 8 9 -1000 1200 0.91107 0.900525 0.890102 0.879799 0.869616 0.859551 0.849602 0.839768 0.830048 0.820441 0.810945 0.801558 0.792281 -911.07 1080.63 356.0406 263.9397 869.6159 -1031.46 -339.841 -251.93 830.0482 984.529 324.3779 240.4675 -792.281 400 V 18 400 19 300 W 20 -1000 X 4 5 6 7 8 A 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 B 1 2 Problem 3. Reconsider the series of cash flows from Problem 2- - 3 Cumulative Month Amount D 0 - 1 E 1200 2 F 600 3 G 4 300 -1000 H 5 -1200 I 6 J 7 K 8 -400 -300 -1000 L 9 CF (1,000s of $) $400.00 -700 What uniform periodic payment would you need in order to produce the same NPV as you found in Problem 2 if the MARR yields 15%? This is an ordinary annuity (or an annuity in arrears), with a monthly payment schedule. Caution - Remember to adjust the units for the MARR. Also, watch the signs of your results. A. Use an appropriate equation from Peterson to compute the uniform monthly payment required to produce the same NPV. B. Then, separately, use Excel's PMT function to compute the same value. C. Finally, separately lay out that stream of 20 identical payments and use it to check your results by the same methods as in Problem 2. That is, use the CF stream of that ordinary annuity to compute the NPV. 1200 M 10 400 N 11 O 12 300 1000 P 13 -1200 14 Now, what if we change this to an annuity due (or an annuity in advance) and we change the payment schedule to annual? What series of uniform payments would we need to provide the same NPV, given that we have a MARR of 15%? D. Rearrange and use an appropriate equation from Peterson to compute the annual payments required to produce the same NPV. E. Separately, use Excel's PMT function to compute the same value. F. Use the CF stream of that annuity due to compute the NPV. R Note The correct result is the same as Peterson's Annual Equivalent method. (Well... the same except that you have computed a Monthly Equivalent here). 15 -400 -300 S 16 1000 T 17 1200 U 18 400 V 19 W 20 300 -1000 X 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 A 22 23 24 25 26 1 2 Problem 4. Consider the following series of end of period cash flows - 3 B Cumulative End of Year Amount CF (1,000s of $) C b) 0 70,000 -100,000 D a) Payback period with interest 4.25 E 1 years 2 F -10,000 3 0 G 4 + H 0 20,000 30,000 40,000 40,000 40,000 5 - C. Plot the results of A and B together in one graph. Use Excel's XY (scatter) plotting feature. 10000 6 A. Find the payback period without interest (also known as "undiscounted payout" or just plain "payout") B. The company can borrow funds at 6% and management requires a profit margin of 4%. Find the payback period with interest (also known as the "discounted payback period" or "discounted payout") J K L M N O 4 5 6 7 8 9 10 A 11 12 B C D E F G H J K L M 1 2 Problem 5. Your company buys a truck for $17,000. To make this purchase, your company takes a loan at 11% APR for 60 months. 3 What schedule of monthly principal, interest and total payments must your company make, month-by-month and cumulative, to service the loan over its life? Provide the amortization schedule (using excel functions or A. equations from Peterson). N If your company had taken the loan for only 48 months instead of the 60 months, what would be the differences in the monthly total payment and the cumulative interest payments compared with the 60 month loan that you actually got? Provide the amortization schedule (using excel functions or equations from B. Peterson). O H Problem 1. A) Determine the MARR for a company that can invest excess funds at 6% and requires 7% profit margin. B) What if, instead, the company borrows funds at 9%? a) MARR 13.00% b) MARR 16.00% 4 5 6 7 8 9 10 11 12 13 14 1 2 Problem 2. Consider the following series of cash flows: 3 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 32 33 A 34 35 36 37 B Example → C —— 0 b) NPV c) D -700 n = 20 IA = 0.15 İM = 1.171% Month Cumulative Undiscounted CF = 400 Discount (or PW) Factors = Discounted CFs = NPV by summing DCFs = $248.89 = Example 1 E 1200 2 F 600 Cumulative Month Amount CF (1,000s of $) $400.00 What is the NPV if the MARR yields 15%? Compute your solution by two (2) methods as follows: Example. Compute the PV month-by-month and sum those monthly results to find the NPV. I've done this for you as an example. A. Read through the example provided in the "Example" tab. Explicitly state that you reviewed the example and understand what is happening. B. Then, separately, use Excel's NPV function. 3 G 300 4 H -1000 5 I -1200 6 J -400 7 -300 K months % per year = MARRAnnual - This is an effective annual interest rate. % per month = MARR Monthly This is an effective monthly interest rate. 0 -700 1 1200 2 600 3 300 4 5 6 7 -1000 -1200 -400 -300 1 0.988426 0.976985042 0.965677 0.9545 0.943452 0.932532 0.921738 -700 1186.111 586.1910253 289.7031 -954.5 -1132.14 -373.013 -276.522 8 The example was reviewed and understood. The period contracted for the loan is 20 months with an annual interest rate of 15.00% and an effective MARR monthly interest rate at 1.171%. Undiscounted CF does not take into consideration the value of time. So for example, for year 0 the value is $700.00. With a discount factor of 1, the discounted CF would remain the same. With respect to year 1, if you 30 receive $1200.00 1 year from now, undiscounted today would be $1200 dollars, but since inflation/other factors occurs, that 1200 dollars 1 31 year later will not be $1200 dollars today. It will be a little less than $1200. With a discounted factor of 0.988421, the $1200 will be worth $1186.105. This applies to all the other years till year 20. -1000 L 9 M 1200 10 400 N 11 10 400 300 O C. Now recompute the NPV at a MARR of 65%. I know that is awfully high, but see what happens. Comment about the difference. D. Compute the FV at the end of month 20 at a MARR of 15%. Note- I do not know of an Excel function that performs this calculation. 12 11 300 1000 P 12 1000 13 -1200 Q 14 -400 R 14 -400 15 -300 S 15 -300 16 1000 T 16 1000 17 1200 U 17 1200 18 19 20 13 -1200 300 -1000 8 9 -1000 1200 0.91107 0.900525 0.890102 0.879799 0.869616 0.859551 0.849602 0.839768 0.830048 0.820441 0.810945 0.801558 0.792281 -911.07 1080.63 356.0406 263.9397 869.6159 -1031.46 -339.841 -251.93 830.0482 984.529 324.3779 240.4675 -792.281 400 V 18 400 19 300 W 20 -1000 X 4 5 6 7 8 A 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 B 1 2 Problem 3. Reconsider the series of cash flows from Problem 2- - 3 Cumulative Month Amount D 0 - 1 E 1200 2 F 600 3 G 4 300 -1000 H 5 -1200 I 6 J 7 K 8 -400 -300 -1000 L 9 CF (1,000s of $) $400.00 -700 What uniform periodic payment would you need in order to produce the same NPV as you found in Problem 2 if the MARR yields 15%? This is an ordinary annuity (or an annuity in arrears), with a monthly payment schedule. Caution - Remember to adjust the units for the MARR. Also, watch the signs of your results. A. Use an appropriate equation from Peterson to compute the uniform monthly payment required to produce the same NPV. B. Then, separately, use Excel's PMT function to compute the same value. C. Finally, separately lay out that stream of 20 identical payments and use it to check your results by the same methods as in Problem 2. That is, use the CF stream of that ordinary annuity to compute the NPV. 1200 M 10 400 N 11 O 12 300 1000 P 13 -1200 14 Now, what if we change this to an annuity due (or an annuity in advance) and we change the payment schedule to annual? What series of uniform payments would we need to provide the same NPV, given that we have a MARR of 15%? D. Rearrange and use an appropriate equation from Peterson to compute the annual payments required to produce the same NPV. E. Separately, use Excel's PMT function to compute the same value. F. Use the CF stream of that annuity due to compute the NPV. R Note The correct result is the same as Peterson's Annual Equivalent method. (Well... the same except that you have computed a Monthly Equivalent here). 15 -400 -300 S 16 1000 T 17 1200 U 18 400 V 19 W 20 300 -1000 X 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 A 22 23 24 25 26 1 2 Problem 4. Consider the following series of end of period cash flows - 3 B Cumulative End of Year Amount CF (1,000s of $) C b) 0 70,000 -100,000 D a) Payback period with interest 4.25 E 1 years 2 F -10,000 3 0 G 4 + H 0 20,000 30,000 40,000 40,000 40,000 5 - C. Plot the results of A and B together in one graph. Use Excel's XY (scatter) plotting feature. 10000 6 A. Find the payback period without interest (also known as "undiscounted payout" or just plain "payout") B. The company can borrow funds at 6% and management requires a profit margin of 4%. Find the payback period with interest (also known as the "discounted payback period" or "discounted payout") J K L M N O 4 5 6 7 8 9 10 A 11 12 B C D E F G H J K L M 1 2 Problem 5. Your company buys a truck for $17,000. To make this purchase, your company takes a loan at 11% APR for 60 months. 3 What schedule of monthly principal, interest and total payments must your company make, month-by-month and cumulative, to service the loan over its life? Provide the amortization schedule (using excel functions or A. equations from Peterson). N If your company had taken the loan for only 48 months instead of the 60 months, what would be the differences in the monthly total payment and the cumulative interest payments compared with the 60 month loan that you actually got? Provide the amortization schedule (using excel functions or equations from B. Peterson). O H Problem 1. A) Determine the MARR for a company that can invest excess funds at 6% and requires 7% profit margin. B) What if, instead, the company borrows funds at 9%? a) MARR 13.00% b) MARR 16.00% 4 5 6 7 8 9 10 11 12 13 14 1 2 Problem 2. Consider the following series of cash flows: 3 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 32 33 A 34 35 36 37 B Example → C —— 0 b) NPV c) D -700 n = 20 IA = 0.15 İM = 1.171% Month Cumulative Undiscounted CF = 400 Discount (or PW) Factors = Discounted CFs = NPV by summing DCFs = $248.89 = Example 1 E 1200 2 F 600 Cumulative Month Amount CF (1,000s of $) $400.00 What is the NPV if the MARR yields 15%? Compute your solution by two (2) methods as follows: Example. Compute the PV month-by-month and sum those monthly results to find the NPV. I've done this for you as an example. A. Read through the example provided in the "Example" tab. Explicitly state that you reviewed the example and understand what is happening. B. Then, separately, use Excel's NPV function. 3 G 300 4 H -1000 5 I -1200 6 J -400 7 -300 K months % per year = MARRAnnual - This is an effective annual interest rate. % per month = MARR Monthly This is an effective monthly interest rate. 0 -700 1 1200 2 600 3 300 4 5 6 7 -1000 -1200 -400 -300 1 0.988426 0.976985042 0.965677 0.9545 0.943452 0.932532 0.921738 -700 1186.111 586.1910253 289.7031 -954.5 -1132.14 -373.013 -276.522 8 The example was reviewed and understood. The period contracted for the loan is 20 months with an annual interest rate of 15.00% and an effective MARR monthly interest rate at 1.171%. Undiscounted CF does not take into consideration the value of time. So for example, for year 0 the value is $700.00. With a discount factor of 1, the discounted CF would remain the same. With respect to year 1, if you 30 receive $1200.00 1 year from now, undiscounted today would be $1200 dollars, but since inflation/other factors occurs, that 1200 dollars 1 31 year later will not be $1200 dollars today. It will be a little less than $1200. With a discounted factor of 0.988421, the $1200 will be worth $1186.105. This applies to all the other years till year 20. -1000 L 9 M 1200 10 400 N 11 10 400 300 O C. Now recompute the NPV at a MARR of 65%. I know that is awfully high, but see what happens. Comment about the difference. D. Compute the FV at the end of month 20 at a MARR of 15%. Note- I do not know of an Excel function that performs this calculation. 12 11 300 1000 P 12 1000 13 -1200 Q 14 -400 R 14 -400 15 -300 S 15 -300 16 1000 T 16 1000 17 1200 U 17 1200 18 19 20 13 -1200 300 -1000 8 9 -1000 1200 0.91107 0.900525 0.890102 0.879799 0.869616 0.859551 0.849602 0.839768 0.830048 0.820441 0.810945 0.801558 0.792281 -911.07 1080.63 356.0406 263.9397 869.6159 -1031.46 -339.841 -251.93 830.0482 984.529 324.3779 240.4675 -792.281 400 V 18 400 19 300 W 20 -1000 X 4 5 6 7 8 A 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 B 1 2 Problem 3. Reconsider the series of cash flows from Problem 2- - 3 Cumulative Month Amount D 0 - 1 E 1200 2 F 600 3 G 4 300 -1000 H 5 -1200 I 6 J 7 K 8 -400 -300 -1000 L 9 CF (1,000s of $) $400.00 -700 What uniform periodic payment would you need in order to produce the same NPV as you found in Problem 2 if the MARR yields 15%? This is an ordinary annuity (or an annuity in arrears), with a monthly payment schedule. Caution - Remember to adjust the units for the MARR. Also, watch the signs of your results. A. Use an appropriate equation from Peterson to compute the uniform monthly payment required to produce the same NPV. B. Then, separately, use Excel's PMT function to compute the same value. C. Finally, separately lay out that stream of 20 identical payments and use it to check your results by the same methods as in Problem 2. That is, use the CF stream of that ordinary annuity to compute the NPV. 1200 M 10 400 N 11 O 12 300 1000 P 13 -1200 14 Now, what if we change this to an annuity due (or an annuity in advance) and we change the payment schedule to annual? What series of uniform payments would we need to provide the same NPV, given that we have a MARR of 15%? D. Rearrange and use an appropriate equation from Peterson to compute the annual payments required to produce the same NPV. E. Separately, use Excel's PMT function to compute the same value. F. Use the CF stream of that annuity due to compute the NPV. R Note The correct result is the same as Peterson's Annual Equivalent method. (Well... the same except that you have computed a Monthly Equivalent here). 15 -400 -300 S 16 1000 T 17 1200 U 18 400 V 19 W 20 300 -1000 X 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 A 22 23 24 25 26 1 2 Problem 4. Consider the following series of end of period cash flows - 3 B Cumulative End of Year Amount CF (1,000s of $) C b) 0 70,000 -100,000 D a) Payback period with interest 4.25 E 1 years 2 F -10,000 3 0 G 4 + H 0 20,000 30,000 40,000 40,000 40,000 5 - C. Plot the results of A and B together in one graph. Use Excel's XY (scatter) plotting feature. 10000 6 A. Find the payback period without interest (also known as "undiscounted payout" or just plain "payout") B. The company can borrow funds at 6% and management requires a profit margin of 4%. Find the payback period with interest (also known as the "discounted payback period" or "discounted payout") J K L M N O 4 5 6 7 8 9 10 A 11 12 B C D E F G H J K L M 1 2 Problem 5. Your company buys a truck for $17,000. To make this purchase, your company takes a loan at 11% APR for 60 months. 3 What schedule of monthly principal, interest and total payments must your company make, month-by-month and cumulative, to service the loan over its life? Provide the amortization schedule (using excel functions or A. equations from Peterson). N If your company had taken the loan for only 48 months instead of the 60 months, what would be the differences in the monthly total payment and the cumulative interest payments compared with the 60 month loan that you actually got? Provide the amortization schedule (using excel functions or equations from B. Peterson). O H Problem 1. A) Determine the MARR for a company that can invest excess funds at 6% and requires 7% profit margin. B) What if, instead, the company borrows funds at 9%? a) MARR 13.00% b) MARR 16.00% 4 5 6 7 8 9 10 11 12 13 14 1 2 Problem 2. Consider the following series of cash flows: 3 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 32 33 A 34 35 36 37 B Example → C —— 0 b) NPV c) D -700 n = 20 IA = 0.15 İM = 1.171% Month Cumulative Undiscounted CF = 400 Discount (or PW) Factors = Discounted CFs = NPV by summing DCFs = $248.89 = Example 1 E 1200 2 F 600 Cumulative Month Amount CF (1,000s of $) $400.00 What is the NPV if the MARR yields 15%? Compute your solution by two (2) methods as follows: Example. Compute the PV month-by-month and sum those monthly results to find the NPV. I've done this for you as an example. A. Read through the example provided in the "Example" tab. Explicitly state that you reviewed the example and understand what is happening. B. Then, separately, use Excel's NPV function. 3 G 300 4 H -1000 5 I -1200 6 J -400 7 -300 K months % per year = MARRAnnual - This is an effective annual interest rate. % per month = MARR Monthly This is an effective monthly interest rate. 0 -700 1 1200 2 600 3 300 4 5 6 7 -1000 -1200 -400 -300 1 0.988426 0.976985042 0.965677 0.9545 0.943452 0.932532 0.921738 -700 1186.111 586.1910253 289.7031 -954.5 -1132.14 -373.013 -276.522 8 The example was reviewed and understood. The period contracted for the loan is 20 months with an annual interest rate of 15.00% and an effective MARR monthly interest rate at 1.171%. Undiscounted CF does not take into consideration the value of time. So for example, for year 0 the value is $700.00. With a discount factor of 1, the discounted CF would remain the same. With respect to year 1, if you 30 receive $1200.00 1 year from now, undiscounted today would be $1200 dollars, but since inflation/other factors occurs, that 1200 dollars 1 31 year later will not be $1200 dollars today. It will be a little less than $1200. With a discounted factor of 0.988421, the $1200 will be worth $1186.105. This applies to all the other years till year 20. -1000 L 9 M 1200 10 400 N 11 10 400 300 O C. Now recompute the NPV at a MARR of 65%. I know that is awfully high, but see what happens. Comment about the difference. D. Compute the FV at the end of month 20 at a MARR of 15%. Note- I do not know of an Excel function that performs this calculation. 12 11 300 1000 P 12 1000 13 -1200 Q 14 -400 R 14 -400 15 -300 S 15 -300 16 1000 T 16 1000 17 1200 U 17 1200 18 19 20 13 -1200 300 -1000 8 9 -1000 1200 0.91107 0.900525 0.890102 0.879799 0.869616 0.859551 0.849602 0.839768 0.830048 0.820441 0.810945 0.801558 0.792281 -911.07 1080.63 356.0406 263.9397 869.6159 -1031.46 -339.841 -251.93 830.0482 984.529 324.3779 240.4675 -792.281 400 V 18 400 19 300 W 20 -1000 X 4 5 6 7 8 A 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 B 1 2 Problem 3. Reconsider the series of cash flows from Problem 2- - 3 Cumulative Month Amount D 0 - 1 E 1200 2 F 600 3 G 4 300 -1000 H 5 -1200 I 6 J 7 K 8 -400 -300 -1000 L 9 CF (1,000s of $) $400.00 -700 What uniform periodic payment would you need in order to produce the same NPV as you found in Problem 2 if the MARR yields 15%? This is an ordinary annuity (or an annuity in arrears), with a monthly payment schedule. Caution - Remember to adjust the units for the MARR. Also, watch the signs of your results. A. Use an appropriate equation from Peterson to compute the uniform monthly payment required to produce the same NPV. B. Then, separately, use Excel's PMT function to compute the same value. C. Finally, separately lay out that stream of 20 identical payments and use it to check your results by the same methods as in Problem 2. That is, use the CF stream of that ordinary annuity to compute the NPV. 1200 M 10 400 N 11 O 12 300 1000 P 13 -1200 14 Now, what if we change this to an annuity due (or an annuity in advance) and we change the payment schedule to annual? What series of uniform payments would we need to provide the same NPV, given that we have a MARR of 15%? D. Rearrange and use an appropriate equation from Peterson to compute the annual payments required to produce the same NPV. E. Separately, use Excel's PMT function to compute the same value. F. Use the CF stream of that annuity due to compute the NPV. R Note The correct result is the same as Peterson's Annual Equivalent method. (Well... the same except that you have computed a Monthly Equivalent here). 15 -400 -300 S 16 1000 T 17 1200 U 18 400 V 19 W 20 300 -1000 X 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 A 22 23 24 25 26 1 2 Problem 4. Consider the following series of end of period cash flows - 3 B Cumulative End of Year Amount CF (1,000s of $) C b) 0 70,000 -100,000 D a) Payback period with interest 4.25 E 1 years 2 F -10,000 3 0 G 4 + H 0 20,000 30,000 40,000 40,000 40,000 5 - C. Plot the results of A and B together in one graph. Use Excel's XY (scatter) plotting feature. 10000 6 A. Find the payback period without interest (also known as "undiscounted payout" or just plain "payout") B. The company can borrow funds at 6% and management requires a profit margin of 4%. Find the payback period with interest (also known as the "discounted payback period" or "discounted payout") J K L M N O 4 5 6 7 8 9 10 A 11 12 B C D E F G H J K L M 1 2 Problem 5. Your company buys a truck for $17,000. To make this purchase, your company takes a loan at 11% APR for 60 months. 3 What schedule of monthly principal, interest and total payments must your company make, month-by-month and cumulative, to service the loan over its life? Provide the amortization schedule (using excel functions or A. equations from Peterson). N If your company had taken the loan for only 48 months instead of the 60 months, what would be the differences in the monthly total payment and the cumulative interest payments compared with the 60 month loan that you actually got? Provide the amortization schedule (using excel functions or equations from B. Peterson). O H

Answer rating: 100% (QA)

Answers A MARR is an abbreviation for Minimum Acceptable ... View the full answer