Microsoft Office Tutorials and References
In Depth Information
Summary information
Because the user can now change the term and make additional payments; the maturity date isn't always fixed.
The formulas are set up for a maximum of 360 payments, but not all these rows need summed. For the summary
information, you want to sum only the rows up until the loan is paid off. The formula in D13 is
=SUMIF(\$H15:\$H374,”>=–1”,D15:D374)
After the Balance in column H is zero, the amortization is complete. This SUMIF function sums only those pay-
ments up until that point. This formula is copied across to the next three columns. Note that the condition is
“greater than or equal to –1.” This handles the situation in which the final balance isn't exactly zero (but very
close to it).
Changing the APR
If the interest rate changes, the user can enter the new rate in the APR column. That new rate is in effect until a
different rate is entered.
The formula in cell C15 retrieves the Starting_Rate value from the input area. The formula in cell C16, and
copied down, is
=C15
If a new rate is entered, it overwrites the formula, and that new rate is propagated down the column. The effect
is that the new rate is in effect for all subsequent payments — at least, until it changes again.
In Figure 13-2, the rate was changed to 5.75% beginning with the seventh payment. The lower rate also affected
the payments. The rate changed again beginning with the 13th payment, and again the payment amount was ad-
justed.
I used conditional formatting for the cells in the APR column to make the rate changes
stand out.
When the APR is changed, the payment amount is adjusted. The loan is essentially re-amortized for the remain-
ing term, using the new interest rate. The formula in cell D16 is
=IF(C16<>C15,–PMT(C16/12,(Term*12)–A15,H15),D15)
This formula checks the value in the APR column. If it's different than the APR in the previous row, the PMT
function calculates the new payment. If the APR hasn't changed the previous payment is returned.