please help with excel

(4) Fill in the sheet titled "Loan Analysis" but ONLY include cash-flow related to Ann's loan in the sheet "NPV-IRR". Leave cells unrelated to the loan cash flow blank. (4.a) What is the annualized IRR for Ann's loan? (4.b) is the IRR for Ann's loan higher or lower than the advertised loan contract rate? (4.c) Why? (4.d) Plot Ann's NPV for Ann's loan, for discount rates 0%-10%. Copy and paste the chart below. C D E F G H H K 1 Save 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 B 1 Inputs 2 Purchase Price $11,822,261.54 3 Purchase Costs 1.50% 4 Loan amount $0.00 5 Loan Closing Costs 1.25% 6 Prepayment Penalty 5%/4%/3%/2%/19/0% 543/ 7 Debt Service $0.00 8 NOI 2015 $768,447.00 9 Going in cap rate 2014 6.50% 10 NOI growth rate 2.00% 11 Exit cap rate 2019 6.2596 12 Selling Costs 7.00% 13 14 15 16 17 18 19 20 21 22 22 24 25 26 27 28 29 30 31 32 39 34 35 36 37 38 Ciese Underwriting NPV-IRR IRR NPV of the Loan $1.00 SO SO 50 Savo Discount Rate NPV 0.009 50 100% 1.50 12.009 2. 30 1,500 4100% 4503 5.00 % 3.30 6 30% 6.598 4.00% 9.569 S0.00 $0.00 $0.00 $0.00 $0.00 50.00 $0.00 $0.00 50.00 S0.00 S0.00 $0.00 $0.00 50.00 $0.00 SV.00 50 60 5050 50.40 50.30 50 20 5010 Loan Analysis + D E G H M N Sale Price Selling Costs Loan Balance Repaid Loan Prepayment Penalty Net Cash Flow IRR NPV of the Loan $100 $0.90 $0.80 Discount Rate NPV 0.00% 0.5096 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.0098 4.50% 5,00% 5.5096 6.0096 6.50% 7.0056 7.5096 8.3096 8.50% 9.0099 9.500 10.00% $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 50.00 $0.00 $0.00 S0.00 $0.00 SO 70 50.60 U $0.50 > $0.00 50.00 50.30 $0.20 $0.10 $0.00 S6.00 $0.00 S0.00 50.00 $0.00 $0.00 $0.00 $0.00 S0.00 50% 200 ED 00 2 Discount Rate 3.0 3.50 200% 4 SD 5509 6.00% 6.50% 7.00X 7.50% 8.00% 8.50% 9.00% 9.50% 10.00% (4) Fill in the sheet titled "Loan Analysis" but ONLY include cash-flow related to Ann's loan in the sheet "NPV-IRR". Leave cells unrelated to the loan cash flow blank. (4.a) What is the annualized IRR for Ann's loan? (4.b) is the IRR for Ann's loan higher or lower than the advertised loan contract rate? (4.c) Why? (4.d) Plot Ann's NPV for Ann's loan, for discount rates 0%-10%. Copy and paste the chart below. C D E F G H H K 1 Save 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 B 1 Inputs 2 Purchase Price $11,822,261.54 3 Purchase Costs 1.50% 4 Loan amount $0.00 5 Loan Closing Costs 1.25% 6 Prepayment Penalty 5%/4%/3%/2%/19/0% 543/ 7 Debt Service $0.00 8 NOI 2015 $768,447.00 9 Going in cap rate 2014 6.50% 10 NOI growth rate 2.00% 11 Exit cap rate 2019 6.2596 12 Selling Costs 7.00% 13 14 15 16 17 18 19 20 21 22 22 24 25 26 27 28 29 30 31 32 39 34 35 36 37 38 Ciese Underwriting NPV-IRR IRR NPV of the Loan $1.00 SO SO 50 Savo Discount Rate NPV 0.009 50 100% 1.50 12.009 2. 30 1,500 4100% 4503 5.00 % 3.30 6 30% 6.598 4.00% 9.569 S0.00 $0.00 $0.00 $0.00 $0.00 50.00 $0.00 $0.00 50.00 S0.00 S0.00 $0.00 $0.00 50.00 $0.00 SV.00 50 60 5050 50.40 50.30 50 20 5010 Loan Analysis + D E G H M N Sale Price Selling Costs Loan Balance Repaid Loan Prepayment Penalty Net Cash Flow IRR NPV of the Loan $100 $0.90 $0.80 Discount Rate NPV 0.00% 0.5096 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.0098 4.50% 5,00% 5.5096 6.0096 6.50% 7.0056 7.5096 8.3096 8.50% 9.0099 9.500 10.00% $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 50.00 $0.00 $0.00 S0.00 $0.00 SO 70 50.60 U $0.50 > $0.00 50.00 50.30 $0.20 $0.10 $0.00 S6.00 $0.00 S0.00 50.00 $0.00 $0.00 $0.00 $0.00 S0.00 50% 200 ED 00 2 Discount Rate 3.0 3.50 200% 4 SD 5509 6.00% 6.50% 7.00X 7.50% 8.00% 8.50% 9.00% 9.50% 10.00%