Compare the filled in numbers of ONLY THE FIRST ROW
with your Excel
document, and then
fill in the rest of only the FIRST ROW
of Table 1 with NUMBERS from the highlighted portions of the Excel
For the year 2 (24 months)
and year 5 balances, be careful to look in the A column for the proper
month (NOT the gray markers left of it, which are numbered differently than the
month - for example, grey 24 is only month 19) and then
look at the corresponding E value for the loan balance; I have highlighted
these in yellow to help you find them and I've included the correct
year 5 balance to ensure you have the correct numbers!
Before I finalized
all the bank payment numbers,
I found out that a deceased relative was leaving me some money,
which would arrive before I closed the loan.
president gave me two options.
|1st yr total |
|Year 2 balance
Year 5 balance
|total interest paid
|Option 2 + $20|
Pay Extra $20
||See #9 first:
Option 1: Take out
a smaller loan
I could reduce the loan amount by $2000 (ie change B2 to read
=.8*B1-2000 in the FORMULA BAR and then hit enter - do not change the amount in the B2
box itself - it is much easier to change it in the formula bar).
Notice that this will change the
rest of the amortization table.
Verify that you have the same year 5 balance as in the chart and then
fill in ONLY THE SECOND ROW of table 1 with numbers.
Option 2: Buy down the rate
I could leave the loan amount the same, and pay
to reduce the rate from 6.75% down to 6.25%.
So change D1 to read =.0625/12 by doing this in the FORMULA BAR
after clicking on D1. Click on B2 and change it back to =.8*B1
in the FORMULA BAR.
Verify that you have the same year 5 balance as on the table
and then fill in ONLY THE THIRD ROW of
Paying extra each month on option 2.
If I choose option 2, then I have a lower monthly payment.
Perhaps I can afford to pay more per month. Let's investigate this to
see what happens.
Using option 2, we'll see what happens if I
can pay an extra $20 each month.
B3 changes via adding +20 at the end
to =PMT(D1,360,-B2) + 20. Do NOT fill anything in on paper yet.