Amortization w/CUMIPMT and CUMPRINC

Introduction

This tutorial will guide you through the process of calculating loan amortization using Excel functions CUMIPMT and CUMPRINC. By the end of this tutorial, you'll understand how to create an amortization schedule that includes cumulative interest and cumulative principal, which is essential for managing loans effectively.

Step 1: Determine Loan Parameters

Before creating the amortization table, establish the loan parameters.

• Monthly Budget: $625
• Interest Rate: 4.25% per annum
• Loan Term: 6 years

Calculate Present Value

To find out the maximum loan amount you can borrow, use the present value function:

• Divide the annual interest rate by 12 to get the monthly interest rate.
• Multiply the number of years by 12 to get the total number of payments.
• Set the periodic payment as a negative value.

The formula in Excel:

=PV(rate, nper, pmt)

Where:

• rate = 4.25%/12
• nper = 6*12
• pmt = -625

The result will be the maximum loan amount, which is approximately $39,659.

Step 2: Set Up the Amortization Schedule

Create a basic amortization schedule in Excel.

Create Payment Numbers

1. In Column A, create payment numbers from 1 to 72 using the fill series feature.

Initialize Columns

Set up the following columns:

• Beginning Balance
• Monthly Payment
• Interest Paid
• Principal Paid
• Ending Balance

Populate Initial Values

• For the Beginning Balance of the first payment, use the present value calculated earlier.
• For the Monthly Payment, enter $625 as an absolute reference.

Step 3: Calculate Interest and Principal Payments

Use either the manual calculation or Excel functions to compute interest and principal payments.

Using Manual Calculation

1. Calculate interest for the first payment:
   • Interest = Beginning Balance × Monthly Interest Rate
2. Calculate principal:
   • Principal = Monthly Payment - Interest
3. Update the Ending Balance:
   • Ending Balance = Beginning Balance - Principal

Using Excel Functions

Alternatively, you can use the IPMT function to find interest:

=IPMT(rate, per, nper, pv)

Where:

• rate = Monthly interest rate
• per = Payment number
• nper = Total number of payments
• pv = Present value (as a negative)

For principal payments, you can use:

=PPMT(rate, per, nper, pv)

You can also calculate principal directly from the monthly payment and interest.

Step 4: Fill Down Formulas

Once the first row is complete:

• Fill down the formulas for interest, principal, and ending balance for all 72 payments.
• The beginning balance for the next payment is the ending balance from the previous row.

Step 5: Calculate Cumulative Interest and Principal

Add two new columns for cumulative interest and cumulative principal.

Use CUMIPMT for Cumulative Interest

The formula for cumulative interest:

=CUMIPMT(rate, nper, pv, start_period, end_period, type)

Where:

• start_period = 1 (first payment)
• end_period = current payment number

Use CUMPRINC for Cumulative Principal

The formula for cumulative principal:

=CUMPRINC(rate, nper, pv, start_period, end_period, type)

This will give you a running total of the interest and principal paid up to each payment.

Conclusion

You have successfully created an amortization schedule that details the breakdown of monthly payments including cumulative interest and principal. This knowledge is crucial for understanding your loan obligations and planning your finances effectively. For further learning, explore advanced Excel functions or consider applying these principles to different loan scenarios.