Formulas and Functions

Table Of Contents

Chapter 6 Financial Functions 11 3

end (0): Payments are due at the end of each period.

beginning (1): Payments are due at the beginning of each period.

Examples

It is generally understood that the amount of the principal reduction on a loan is higher in the later

years, as compared to the early years. This example demonstrates just how much higher the later

years can be. Assume a mortgage loan with an initial loan amount of $550,000, an interest rate of 6%,

and a 30-year term.

The CUMPRINC function can be used to determine the interest for any period. In the following table,

CUMPRINC has been used to determine the principal repaid in the rst year (payments 1 through 12)

and in the last year (payments 349 through 360) of the loan term. The function evaluates to $6,754.06

and $38,313.75, respectively. The amount of principal paid in the rst year is only about 18% of the

amount of principal paid in the last year.

periodic-rate num-periods present-value starting-per ending-per when-due

=CUMPRINC

(B2, C2, D2, E2,

F2, G2)

=0.06/12 360 =550000 1 12 0

=CUMPRINC

(B2, C2, D2, E3,

F3, G2)

349 360