In the example, the first value for the Principal PMT was 199.10 in cell E18. Answers for the questions must follow the example answer. 

 

(1-2 use first picture) 

1. Calculate the Principal PMT and Balloon Payment using IF statements.  with respect to cell C12

2. Calculate the Principal PMT and Balloon Payment using True/False Statements (Boolean Expressions (T/F))

 

(3-5 use second picture)

3. Calculate the Principal PMT and Balloon Payment using Vlookup Function with respect to cell C6

4. Calculate the Principal PMT and Balloon Payment using IndexMatch with respect to cell C6

5. Calculate the Principal PMT and Balloon Payment using OffsetMatch function with respect to cell C6 

 

4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 no 4 6 7 B Inputs Original Balance Maturity Frequency Contract Rate Points Prepayment Penalty Moving Date Month 0 1 Inputs 2 Moving Date 3 3 4 5 Month 4 5 6 7 8 9 10 41 Month 0 1 2 6 7 8 9 10 Model 0 1 96 97 P 200,000 30 12 6.0% 2 5.0% 8 Model Balance 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000,00 200 000 00 VLOOKUP Table Payment Balance 8 1.00 1.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 Comments: Please use only IF() statements in columns E & F to make your model dynamic with respect to cell C12. Interest PMT 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000,00 1,000.00 4.000.00 Balloon Payment Interest PMT 1.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 Principal PMT Balloon Payment Principal PMT Monthly Payment Lender's Yield Prepayment. Penalty Balloon Payment. Monthly Payment Lender's Yield Outputs Outputs $1,199.10 4.56% Lender Cash Flows Prepayment Penalty (196,000) 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 Please use only VLOOKUP() in columns E & F to make your model dynamic with respect to cell C6. 1,000.00 1,000.00 1,000.00 Hint 1: VLOOKUP the month number into the VLOOKUP Table and multiply by the monthly payment minus the interest payment in column E and by the Balloon Payment in Column F. Hint 2: The Balloon Payment is equal to the balance at the beginning of the month minus the principal paid that month. $1,199.10 4.56% Lender Cash Flows (196,000) 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1.000.00