Texts: Please show your work, ANSWERS, and formulas on an Excel screenshot. NO HARDCODED ANSWERS. ONLY EXCEL WORK. ONLY EXCEL SHEETS. If no Excel sheets, I'll rate negative. Thank you so much!
For parts 1-5, you have the option to lease or purchase an asset which will have a 3-year life. You can purchase the asset for $17,500, or you can lease it for 3 years with the first payment of $6,500 due today (the cash flow will be 3 equal payments at the beginning of periods 1, 2, and 3). You can borrow from the bank at 18%. Create a spreadsheet model in Excel to compare each option. It would be super helpful if you could show me the formulas in Excel! Thanks in advance!
1.) What is the NPV of the costs of the lease?
A.) 16,594
B.) -17,060.57
C.) 18,542
D.) 166,77
2.) Use an IF() function to determine your lease vs purchase decision based on NPV of the lease-minus-purchase decision. Select its output here:
A.) Purchase
B.) Lease
3.) Create an additional column that contains the "Lease Minus Purchase" cash flows, and calculate the IRR of the differential cash flows.
A.) 0.061
B.) -0.14875
C.) 0.119
D.) 0.18
4.) You discover that the asset will have a salvage value at the end of year 3 of $1,000. What is the new NPV of the costs of the purchase?
A.) 0.16677
B.) 17,476
C.) 16,653
D.) -16,677
5.) Determine the purchase price at which you would be indifferent between the lease or the purchase decision (given the new salvage value). Using your completed model, substitute the following numbers to approximate the correct answer.
A.) 17,524
B.) 17,476
C.) 23,177
D.) 17,675