cumprinc, not sure how to use it, someone please show me steps ple

[GACKT]

The Teaser Loan Company is a bank that makes loans to home buyers. The bank
has made at promotional rate of 1% p.a. for the first five years. The bank is
now considering increasing the interest rate to 6% p.a. on these loans but
realise that some borrowers may have difficulty in paying their monthly
instalments after the interest rate has been increased. The descriptions of
the columns in my excel sheet are as follow:

Cust Num - A unique number assigned to each customer
Last Name - Customer’s last name
Initial - Customer’s initial
Disposable - Customer’s disposable income at the time of the loan
Property Num - A unique identifier of the property the customer owns
Rep - Customer’s loan representative at the bank

The data about the loans given are shown here. Each loan was for 20 years at
promotional rate of 1% p.a.

Loan Num - A unique number assigned to each loan
Cust Num - Customer number of the borrower
Property Num - The property number of the property
Initial Principal - Initial amount of the loan
Monthly Payment - The monthly instalment

Data about the properties that the borrowers own are shown here:

Property Num - A unique number assigned to each property
Appraised - The appraised valued of the property by the bank when the loan
was given
Current Val - The value of the property as it nears five-year mark

A timeline of the loans is as follows:

Principal Amt Paid so far @ 1%
0 ------------------------------------ 5 Years

Principal amount remaining @ 6%
5 Years ------------------------------------------------- 20 Years

Would anyone be kind enough to show me the steps to calculate the amount of
principal paid off in five years via use of the CUMPRINC() function?

thanks

 

[Bernie Deitrick]

The amount of principal paid off after 5 years of monthly payments is:

=CUMPRINC(APR/12,Years*12,Amount,1,60,0)

For your specifics:

Amount paid off after 5 years:
=CUMPRINC(0.01/12,20*12,InitLoanAmt,1,60,0)

Amount remaining to be paid:

Amount paid off after 5 years:
=InitLoanAmt-CUMPRINC(0.01/12,20*12,InitLoanAmt,1,60,0)


HTH,
Bernie
MS Excel MVP