using the following:

fill in the blanks using excel formulas. use the following directions:

Macro Enable file upon uploading.

Your job is to build an amortization schedule that will allow for extra principal payments. This model could be used by a borrower who wants to put some assumptions in the model and find out how many years it will take to pay off the mortgage.

Assume that the borrower is 30 years old and 2 months as of 2/28/2021 (that is, 10 months until the next birthday). Assume the closing date of the mortgage was December 31, 2020, and the first payment is due January 31, 2021. This means the borrower was exactly 30 on the day of closing.

Also assume that todays date is February 28, 2021.

Anything in gray needs a formula written.

Anything in blue font are inputs ---while building your model, these can be changed to test your model, but should be the original inputs when you turn in your file.

Formula for D4: Use PMT function to compute the monthly payment. Do not hardcode the inputs, but rather pull inputs D1, D2 and D3 in your formula. DO NOT ROUND to 2 decimals (as shown in the videos). We know rounding issues can compound in calculations! PMT(rate,)

Where the ellipsis are: You should put the arguments to the PMT portion of this formula.

Formula for D15: Compute the date assuming the first of the month of the closing date. If the closing date is 12/31/2020, this formula should return 12/1/2020. This is necessary due to the way the DATEDIF function works. More below on this.

Instructions Continued on Next Page

Formula for D6: Use the Index and Match functions on Columns A and H (in the amortization schedule) to determine how many years it will take to pay off the mortgage.

Formula for D7:Years saved = Original term (Cell D3) minus years to pay off (Cell D6)

D9: Use the date function in this form: =DATE(YEAR(D8),MONTH(D8),1)

This should return a date similar to the date in D8, but the first of the month.

Formula for D10: Use the Index and Match functions on Columns H and K to select the date when Paid Off---this date should be the first of the month (even if the actual mortgage is Paid Off at the end of the month). Note: Paid Off as a text in quotes will be in the formula.

Formula for D11: Use the Index and Match functions on Columns C and H to select the date when Paid Off ---this date should be the ACTUAL date of the payoff.

Formula for D14: Write a formula to determine the Age of the borrower when Paid Off. The Datedif function used on the date (D9) and the date when Paid Off (D10) should be added to the persons current age (D12). Also adjust for months old by using D13. Note: D13 has how many months the borrower is AWAY from the next birthday.It is not months old but can be used to determine.

=((D12*12)+(12-D13))/12+DATEDIF(D9,D10,"M")/12

Amortization schedule portion of the file:

Column B: Pull the closing date (in input area) and put this in every row

Column C: Use the Edate function to create the date the PMT is due. Utilize data in Columns A and B.

Column D: Use a combination of ISNUMBER, if/and (If + AND) statements to return what will be paid by the borrower or Paid Off. Logic:If the PRIOR principal balance is a number AND it is also greater than the regular loan payment, then pull the regular payment (Cell D4). If, however, the PRIOR principal balance is a number AND greater than 0, then pull the PRIOR principal balance PLUS this months interest. Otherwise, the cell should return Paid Off. This formula requires two conditions that require the use of ISNUMBER. Note: Do not change Paid Off to Paid off, done, payed off or any other wording. Make it Paid Off. This specificity is for grading purposes.

Column E: This should be an IFERROR statement which determines EITHER the dollar amount of interest paid OR N/A if the mortgage is Paid Off. Do not round. Do not use the IPMT function, but rather calculate the interest based on the prior principal balance and the monthly interest rate. Why? One of the learning objectives of this HW is to understand how the interest each month is calculated. Do not change N/A to n/a or na. Make sure it is N/A (capitalized with hyphen in between). This specificity is for grading purposes.

Instructions Continued on Next Page

Column F: Use IFERROR function to calculate the amount of principal being paid. If the mortgage is Paid Off, this should return N/A Do not change N/A to n/a or na. Make sure it is N/A (capitalized with hyphen in between). This specificity is for grading purposes.

Column G: Using ISNUMBER (and an IF statement), pull D5 or have the formula return N/A. Do not change N/A to n/a or na. Make sure it is N/A (capitalized with hyphen in between). This specificity is for grading purposes.

In addition to the ISNUMBER condition, heres the logic:

If PRIOR principal balance is GREATER than the PRINC paid (not PMT) plus the EXTRA PAYMENT, the cell should return the EXTRA PMT.

IF (PRIOR principal balance LESS PRINC paid) >0, then take this difference; otherwise 0. If none of this is true, N/A

Colum H: Use IFERROR function coupled with an IF statement to return either the dollar amount of the principal balance OR Paid Off.

=IFERROR(If (Prior balance Princ Paid Extra Prin)>0,Prior bal Princ paid Extra payment, Paid Off), Paid Off)

Note you need two Paid Off ---one for the IFERROR and one for the IF.

Note: Do not change Paid Off to Paid off, Paidoff, done, payed off or any other wording. Make it Paid Off. This specificity is for grading purposes.