3 minutes read

Category:

Last updated: 07/03/2020

Simple interest ignores the affects of compounding. Some loans are figured using simple interest, as is the coupon interest you earn from bonds. The formula is:

Where:

r = the interest rate

i = the amount paid in interest every period

p = the amount of money before interest

n = the number of periods (days, weeks, months, etc.)

You can use a calculator, spreadsheet or the back of a napkin. For example, suppose you take out a personal loan for $1,200 and have to pay it back in 12 monthly installments of $234.40 each month. You can calculate the monthly interest rate as

While the formula gives a decimal result, we normally express interest rate as a percentage. Simply multiply the decimal rate by 100 to get the percentage rate:

We usually think of interest rates in annual terms. If necessary, convert your periodic interest rate into an annual one. In our example

This is the interest rate on a loan that charges simple interest, or on an investment that pays simple interest. However, credit cards and other types of loans usually charge compound interest.

Compounding is interest on interest. That is, the balance of your loan includes any interest accumulated from previous periods. Credit cards use daily compounding, meaning they calculate the average daily balance for the billing period (usually 30 days) and multiply it by the daily interest rate. Happily, if you pay off your credit card balance by the due date, you don’t incur any interest charges for the billing period.

Here’s how to calculate the amount you will be charged, on an annual basis, for each period’s unpaid balance:

Your credit card statement shows your APR. Let’s say its 15.99%. You divide the APR by 365 to get the daily rate:

The credit card company will disclose your average daily balance for the period. It does this by summing each day’s balance and dividing by the number of days in the period (30). For example, suppose your credit card summary shows your average daily balance to be $1,500. The daily charge is:

The monthly charge is therefore:

That’s the interest payment you’ll avoid if you pay off your balance before the due date.

The time between the purchase date and the due date is called the grace period. Typically, credit card companies grant a grace period of at least 21 days. However, grace periods are not mandatory, so check the fine print before applying for a credit card.

Also, new cardholders might qualify for a special introductory offer in which interest on purchases is waived for a specified number of months. Interest rates may differ for purchases, cash advances and/or balance transfers.

Mortgages use a complex method of assessing interest, known as amortization. This means that you pay a fixed amount each month for the term of the mortgage, but that the split of how your payment is used changes over time. On your initial payments, almost all the money goes to pay interest on the mortgage.

With each payment, a slightly greater amount is allocated to repayment of principal and a correspondingly smaller amount is charged to interest. Eventually, most of your payment will go to reducing your loan principal.

Here is how the bank figures your mortgage interest. We’ll use an example in which you take out a 30-year, 7% fixed-rate mortgage for $100,000. You’ll need a spreadsheet or calculator to compute mortgage interest.

The amount of your monthly payment is equal to the future value of the loan divided by the number of months in the loan terms (in this case, 360 months). To the lender, your mortgage is an asset with a present value of $100,000. That is, you could pay off your mortgage on Day 1 for $100,000. The value of $100,000 in 30 years (the loan term) is its future value, which depends on the present value and the interest rate. It turns out, the future value of your $100,000 mortgage is $811,552.92. That is, you’ll end up paying $711,552.92 in interest over the 30-year term.

Fortunately, you can use the PMT function to calculate the monthly fixed payment. On an Excel spreadsheet, enter the following formula:

where:

r = the monthly rate

n= the number of periods

pv = the starting mortgage balance

In our example, the monthly payment is:

Note how we divided the APR by 12 to get the monthly interest rate.

You can create an amortization table for your mortgage by setting up the following 5-column x 360-row matrix with the following columns:

Payment Number (1 through 360)

Payment (in this example, each row is $-665.30)

Principal

Interest

Balance

In addition, set up three cells with the following constants:

Monthly interest rate (7%/12)

Number of monthly payments (360)

Starting amount ($-100,000)

You’ll use Excel’s PPMT function (principal payment) and the IPMT function (interest payment). Business calculators have similar functionality.

In the first row:

= 1

Payment number= $-665.30 (the number is negative to indicate money you are paying out)

Payment= PPMT(monthly interest rate, payment number, number of monthly payments, starting amount) = PPMT(7%/12, 1, 360, 100000) = $-81.97

Principal= IPMT(monthly interest rate, payment number, number of monthly payments, starting amount) = IPMT(7%/12, 1, 360, 100000) = $-583.33

Interest= Starting balance – principal for current row = $100,000 - $81.97 = $99,918.03.

Balance

To create the second row, drag the first row down to the second row. Then, change Balance to equal previous row’s Balance – Principal for current row. Finally, drag row 2 down to create 360 payments. The last Balance should be about $0, with some rounding error.

Now you know how much of each payment goes toward interest.