Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Can you show the excel formulas? 8% 7:45 AM module 2_- _davis,_michaels,_and_co - Saved Cameron Batt Home InsertDraw Formulas Data ReviewView 123 1 Module 2
Can you show the excel formulas?
8% 7:45 AM module 2_- _davis,_michaels,_and_co - Saved Cameron Batt Home InsertDraw Formulas Data ReviewView 123 1 Module 2 Student Version 12/18/18 DAVIS, MICHAELS, AND COMPANY Discounted Cash Elow Analysis This case reviews the following discounted cash flow (DCF) techniques 7lump sums, annuities (both ordinary and due), uneven cash flow streams, 8 intra-year compounding and effective annual rates, continuous compounding, and amortization 10 11 The model automatically performs the DCF analyses on the basis of cash 12 flow and interest rate parameters entered in the INPUT DATA section 13 The spreadsheet contains two graphs (1) a plot of EV versus n at different 14 interest rates and (2) a graph f pvs versus n at different interest rates 15 Note that the model stretches out to column K 17 19 Before using the model, it is necessary to fi1l in the empty cells 20 ith the appropriate formulas. Once this is done, the model is ready for use 21 23 24 25 26 INPUT DATA: 27 28 KEY OUTPUT Lump Sum Analysis Lump Sum Analysis 30 Beginning amount 31 Ending amount 32 Interest rate 33 Number of years 34 # periods per year 35 $0.00 $0.00 0.00 0-00% $10,000.00 $20, 000.00 10.00 Future value Present value Effective rate Implied r CASE-01 M PV FV 8% 7:45 AM module 2_- _davis,_michaels,_and_co - Saved Cameron Batt Home InsertDraw Formulas Data ReviewView 123 25 26 INPUT DATA 27 28 KEY OUTPUT Lump Sum Analysis Lump Sum Analysis $10,000.00 $20,000.00 10.00 $0.00 $0.00 30 Beginning amount 31 Ending amount 32 Interest rate 33 Number of years 34 # periods per year 35 36 37 38 Payment 39 Annuity type 40 Ordinary , due=1 41 Interest rate 42 Number of years 43 # periods per year Future value Present value Effective rate Implied r 0-00% 0.00 Annuity Analysis Annuity Analysis $0.00 $0.00 $2,000.00 Future value Present value Effective rate 0-00% 10.00 45 46 47 Interest rate 48 # periods per year 49CF year 50 51 52 53 54 Uneven Payment Analysis Uneven Payment Analysis 10.00 Future value Present value Effective rate $0.00 $0.00 0 $2,000.00 2,000.00 0.00 1,500.00 2,500.00 4,000.00 0-00% Continuous Compounding Analysis Future value Present value Effective rate $0.00 $0.00 0.00 56 57 58 Interest rate Amortization Schedule 10.008 CASE-01 M PV FV 8% 7:45 AM module 2_- _davis,_michaels,_and_co - Saved Cameron Batt File Home InsertDraw Formulas Data ReviewView 56 57 58 Interest rate 59 Loan amount 60 Number of periods 61 62 63 64 65 MODEL-GENERATED DATA Amortization Schedule 10.00 $20, 000.00 GRAPH INPUTS 67 Lump Sum Analysis Future Value Data Table 68 Future Value EIIective rate 70 Future value 71 Present value 72 Implied r for 73 input EV and Pv 74 75 76 03 5% 10% 153 203 253 78 79 80 81 82 83 84 85 86 87 10 Present Value Data Table Present Value 03 5% 10% 153 20% 25% 89 CASE-01 M PV FV 8% 7:45 AM module 2_- _davis,_michaels,_and_co - Saved Cameron Batt File Home InsertDraw Formulas Data ReviewView 105 106 107 108 Effective rate 109 Future value 110 Present value Continuous Compounding Analysis Uses input from INPUT DATA : lump sum section.) 112 113 Amortization Schedule 114 115 116 Repay of Remaining Prin Balance Year Payment Interest 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 $0 $0 $0 END CASE-01 M PV FV 8% 7:45 AM module 2_- _davis,_michaels,_and_co - Saved Cameron Batt File Home InsertDraw Formulas Data ReviewView Present Value Data Table 81 82 83 84 85 86 87 Present Value 03 5% 10% 153 209 253 89 90 91 92 93 Annuity Analysis 94 10 EIIective rate 9 Future value 97 Present value 98 Uneven Payment Stream Analysis 100 101 Effective rate 102 Future value 103 Present value 104 105 Continuous Compounding Analysis 106 107 108 Effective rate 109 Future value 110 Present value (Uses input from INPUT DATA: lump sum section.) 112 113 114 Amortization Schedule Repav CASE-01NM PV FV 8% 7:45 AM module 2_- _davis,_michaels,_and_co - Saved Cameron Batt Home InsertDraw Formulas Data ReviewView 123 1 Module 2 Student Version 12/18/18 DAVIS, MICHAELS, AND COMPANY Discounted Cash Elow Analysis This case reviews the following discounted cash flow (DCF) techniques 7lump sums, annuities (both ordinary and due), uneven cash flow streams, 8 intra-year compounding and effective annual rates, continuous compounding, and amortization 10 11 The model automatically performs the DCF analyses on the basis of cash 12 flow and interest rate parameters entered in the INPUT DATA section 13 The spreadsheet contains two graphs (1) a plot of EV versus n at different 14 interest rates and (2) a graph f pvs versus n at different interest rates 15 Note that the model stretches out to column K 17 19 Before using the model, it is necessary to fi1l in the empty cells 20 ith the appropriate formulas. Once this is done, the model is ready for use 21 23 24 25 26 INPUT DATA: 27 28 KEY OUTPUT Lump Sum Analysis Lump Sum Analysis 30 Beginning amount 31 Ending amount 32 Interest rate 33 Number of years 34 # periods per year 35 $0.00 $0.00 0.00 0-00% $10,000.00 $20, 000.00 10.00 Future value Present value Effective rate Implied r CASE-01 M PV FV 8% 7:45 AM module 2_- _davis,_michaels,_and_co - Saved Cameron Batt Home InsertDraw Formulas Data ReviewView 123 25 26 INPUT DATA 27 28 KEY OUTPUT Lump Sum Analysis Lump Sum Analysis $10,000.00 $20,000.00 10.00 $0.00 $0.00 30 Beginning amount 31 Ending amount 32 Interest rate 33 Number of years 34 # periods per year 35 36 37 38 Payment 39 Annuity type 40 Ordinary , due=1 41 Interest rate 42 Number of years 43 # periods per year Future value Present value Effective rate Implied r 0-00% 0.00 Annuity Analysis Annuity Analysis $0.00 $0.00 $2,000.00 Future value Present value Effective rate 0-00% 10.00 45 46 47 Interest rate 48 # periods per year 49CF year 50 51 52 53 54 Uneven Payment Analysis Uneven Payment Analysis 10.00 Future value Present value Effective rate $0.00 $0.00 0 $2,000.00 2,000.00 0.00 1,500.00 2,500.00 4,000.00 0-00% Continuous Compounding Analysis Future value Present value Effective rate $0.00 $0.00 0.00 56 57 58 Interest rate Amortization Schedule 10.008 CASE-01 M PV FV 8% 7:45 AM module 2_- _davis,_michaels,_and_co - Saved Cameron Batt File Home InsertDraw Formulas Data ReviewView 56 57 58 Interest rate 59 Loan amount 60 Number of periods 61 62 63 64 65 MODEL-GENERATED DATA Amortization Schedule 10.00 $20, 000.00 GRAPH INPUTS 67 Lump Sum Analysis Future Value Data Table 68 Future Value EIIective rate 70 Future value 71 Present value 72 Implied r for 73 input EV and Pv 74 75 76 03 5% 10% 153 203 253 78 79 80 81 82 83 84 85 86 87 10 Present Value Data Table Present Value 03 5% 10% 153 20% 25% 89 CASE-01 M PV FV 8% 7:45 AM module 2_- _davis,_michaels,_and_co - Saved Cameron Batt File Home InsertDraw Formulas Data ReviewView 105 106 107 108 Effective rate 109 Future value 110 Present value Continuous Compounding Analysis Uses input from INPUT DATA : lump sum section.) 112 113 Amortization Schedule 114 115 116 Repay of Remaining Prin Balance Year Payment Interest 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 $0 $0 $0 END CASE-01 M PV FV 8% 7:45 AM module 2_- _davis,_michaels,_and_co - Saved Cameron Batt File Home InsertDraw Formulas Data ReviewView Present Value Data Table 81 82 83 84 85 86 87 Present Value 03 5% 10% 153 209 253 89 90 91 92 93 Annuity Analysis 94 10 EIIective rate 9 Future value 97 Present value 98 Uneven Payment Stream Analysis 100 101 Effective rate 102 Future value 103 Present value 104 105 Continuous Compounding Analysis 106 107 108 Effective rate 109 Future value 110 Present value (Uses input from INPUT DATA: lump sum section.) 112 113 114 Amortization Schedule Repav CASE-01NM PV FVStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started