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.

Handling additional payments

If additional payments are made, they are entered in column E. In Figure 13-2, an additional payment of $500

was applied to the tenth payment. Extra payments are applied to the principal.