Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please show excel solutions (4) Fill in the sheet titled Loan Analysis but ONLY include cash-flow related to Anns loan in the sheet NPV-IRR. Leave
Please show excel solutions
(4) Fill in the sheet titled Loan Analysis but ONLY include cash-flow related to Anns loan in the sheet NPV-IRR. Leave cells unrelated to the loan cash flow blank.
(4.a) What is the annualized IRR for Anns loan?
(4.b) Is the IRR for Anns loan higher or lower than the advertised loan contract rate?
(4.c) Why?
(4.d) Plot Anns NPV for Anns loan, for discount rates 0%-10%. Copy and paste the chart below.
A B 1 Inputs 2 Purchase Price 3 Purchase Costs 4 Loan amount 5 Loan Closing Costs 6 Prepayment Penalty 7 Debt Service 8 NOI 2015 9 Going in cap rate 2014 10 NOI growth rate 11 Exit cap rate 2019 12 Selling Costs $11,822,261.54 3.00% $11,136,913.04 1.65% 5%/4%/3%/2%/1%/0% $640,372.50 $768,447.00 6.50% 2.00% 6.25% 7.00% D E F G H I J Year 2014 2015 2016 2017 2018 2019 2020 Purchase Price Purchase Costs Loan Amount Loan Closing Costs Net Operating Income (NOI) Debt Service Payment Sale Price Selling Costs Loan Balance Repaid Loan Prepayment Penalty Net Cash Flow IRR Discount Rate NPV 0.00% 0.50% 1.00% 1.50% NPV of the Loan $1.00 $0.90 $0.80 $0.70 $0.60 NPV ($) $0.50 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 5.00% 5.50% 6.00% 6.50% 7.00% 7.50% 8.00% 8.50% 9.00% 9.50% 10.00% $0.40 $0.30 $0.20 $0.10 $0.00 0.00% 0.50% 1.00% 3.00% 4.50% 5.50% 8.00% 9.00% 9.50% 2.50% 3.50% 4.00% 5.00% 7.50% 8.50% 1.50% 2.00% 10.00% Discount Rate 6.00% 6.50% 7.00% A B 1 Inputs 2 Purchase Price 3 Purchase Costs 4 Loan amount 5 Loan Closing Costs 6 Prepayment Penalty 7 Debt Service 8 NOI 2015 9 Going in cap rate 2014 10 NOI growth rate 11 Exit cap rate 2019 12 Selling Costs $11,822,261.54 3.00% $11,136,913.04 1.65% 5%/4%/3%/2%/1%/0% $640,372.50 $768,447.00 6.50% 2.00% 6.25% 7.00% D E F G H I J Year 2014 2015 2016 2017 2018 2019 2020 Purchase Price Purchase Costs Loan Amount Loan Closing Costs Net Operating Income (NOI) Debt Service Payment Sale Price Selling Costs Loan Balance Repaid Loan Prepayment Penalty Net Cash Flow IRR Discount Rate NPV 0.00% 0.50% 1.00% 1.50% NPV of the Loan $1.00 $0.90 $0.80 $0.70 $0.60 NPV ($) $0.50 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 5.00% 5.50% 6.00% 6.50% 7.00% 7.50% 8.00% 8.50% 9.00% 9.50% 10.00% $0.40 $0.30 $0.20 $0.10 $0.00 0.00% 0.50% 1.00% 3.00% 4.50% 5.50% 8.00% 9.00% 9.50% 2.50% 3.50% 4.00% 5.00% 7.50% 8.50% 1.50% 2.00% 10.00% Discount Rate 6.00% 6.50% 7.00%Step 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