Question

Mandy would like to buy an apartment and needs a mortgage for $280,000. She was able to qualify for a loan at 7.2% for 30 years. What is the amount of her monthly payment?

Use a spreadsheet program like Microsoft Excel. Start with a blank worksheet.

In your spreadsheet, create a TVM DataFrame

TVM DataFrame

c	n	i	PV	PMT	FV
1

From the problem, fill-in the values for Mandy's mortgage annuity into your spreadsheet. Place a question mark in the column for the variable that needs to be computed. If any value is not used or present, set it equal to zero.

After you have filled-in all of the possible values from the problem, complete the TVM DataFrame by computing the remaining values. For example, suppose that n, the number of years, is in cell B2 and c, the number of compounding periods in a year, is in cell A3. In this case, if you wanted to compute the total number of compounding periods, you would multiply n x c = B2 * A3. Do NOT do the multiplication yourself and type-in the numerical result into the cell. You must COMPUTE the final values using multiplication, division or by reference in the cell of the spreadsheet.

Compute Mandy's monthly payment. Be sure to refer to values in the TFM DataFrame in your computation.

HINT: You could create a worksheet based on the mortgage example that we solved in class. This will help you be sure that you are computing the monthly payment properly. After you replicated the problem in class, then, you can enter the values from this problem "over" the example values.

Finally, create the following two lines in your spreadsheet below your TVM DataFrame:

First Row of Amortization Table

number	Principal	INTEREST	Excess PMT
1

In the box for principal, refer to the cell that has the value (in your TVM DataFrame). For example, if the cell containing the loan amount is D2, then in the principal box above, you would enter: =D2. This tells the spreadsheet to bring the value from D2 to the current cell.

Next, in the INTEREST box, compute the amount of interest that Mandy owes after ONE MONTH. Be sure to use a rate from the TVM DataFrame. Suppose that the cell containing the interest rate is C3. In this case, you should "lock" this cell so that in the formula the reference to the cell is $C$3. On Windows, you can use the F4 key and on MacOS you can use ⌘+T to cycle-through locking {$C$3, C$3, $C3, C3}.

The amount of interest that Mandy owes should be LESS THAN HER MONTHLY PAYMENT. In the Excess PMT box, perform the following computation: from her monthly payment (lock on this cell), subtract the amount of interest that she owes (the cell to the left). This is the amount of her monthly payment in excess of the amount of interest owed.

Because this is an excess amount, Mandy uses it to pay-down her principal so that next month, she no longer owes the full $280,000, but rather owes a little bit less.

Upload your Excel spreadsheet below.

NOTE: A sample grading rubric as an Excel file is attached.

Answer 1

please rate with a thumbs up