Simple Interest Loan Calculator
A Simple Interest Loan or Simple Interest Mortgage is the term used by the mortgage and loan industry to describe a particular type of loan that uses simple interest calculations to accrue interest daily. The interest is calculated as the daily interest rate times the number of days between payments. When payments are missed or are insufficient to cover the interest due, the unpaid interest is tracked as a separate interest balance and not added to the principal.
A Simple Interest Loan provides one of the easiest ways to track the repayment of a loan that involves frequent late payments, early payments, missed payments, extra payments, or partial payments. Why? Two main reasons: (1) the interest calculations are easy to understand and are based on the actual payment dates, (2) the calculations can be implemented within a spreadsheet without much confusion or complexity.
The Simple Interest Loan is particularly useful for people who are not professional lenders, and who are just looking for a simple yet flexible way to offer and track a loan. Continue reading below the download block to learn more about simple interest loans.
Simple Interest Loan Calculator
for Excel
Description
Use this spreadsheet to estimate an amortization schedule and track payments for a Simple Interest Loan. The workbook contains two worksheets. The first is a calculator for creating an amortization schedule and determining the effect of interest rate, payment frequency, and payment amount.
The second worksheet (shown in the screenshot on the right) is a payment tracker that you can use to track actual payments by entering the date and the payment amount.
The interest is calculated based on the date of the payment. Interest is paid first and anything extra is applied to the principal.
In the latest version for Excel, I added a rounding option, balloon payment option, columns for tracking fees, and made many other minor improvements.
How it Works - 3 Main Features of a Simple Interest Loan
1) Simple Interest is Accrued on a Daily Basis
How do you calculate the interest for a Simple Interest Loan? First, the annual rate is converted to a daily rate by dividing the annual rate by 365 (or 360 in some day count conventions). The interest due is calculated as rate*days*principal, where rate is the daily rate, days is the number of days since the last payment, and principal is the amount owed.
Example: Let's say the last payment was made on 1/31/2018 and after applying the payment, the principal balance is $10,000. If the annual rate was 3.65%, then the daily rate would be 3.65%/365 = 0.01%. Let's say the borrower paid $100 on 2/15/2018 (15 days later). The interest would be calculated as 0.01% * 15 * 10000 = $15.00. Assuming that the prior unpaid interest balance was zero, that means that $15 of the $100 payment would be interest and $85 would be principal, making the new principal balance $9915 as of 2/15/2018.
See the simple interest article and calculator to learn more about the simple interest formula.
2) Unpaid Interest is Not Added to the Principal
When a payment is missed or is insufficient to cover the interest, a Simple Interest Loan does not add the unpaid interest to the principal. Instead, the unpaid interest amount is tracked as a separate unpaid interest balance.
It is not uncommon for other types of loans to add unpaid interest to the principal. This is known as negative amortization (paying interest on interest), and is bad news for the borrower.
3) Unpaid Interest is Paid Before the Principal
A simple interest loan is an amortized loan, meaning that you make periodic payments and unpaid interest is paid before your payment is applied to the principal.
If a borrower has been having a hard time making payments, it is possible that their next few payments might not reduce the principal. That is because the unpaid interest balance is paid before the principal. While that might seem harsh, the borrower needs to remember that this situation has occurred because they have made insufficient payments to cover the interest due.
It can be shown that the amortization of the simple interest loan is what makes the monthly payment and total interest nearly the same as a traditional loan (when monthly payments are applied on the same dates as the traditional loan). In other words, the monthly payment and total interest for a simple interest loan can be estimated fairly closely using the compound interest formula.
For the sake of keeping things simple and avoiding a complex debate, note that the term "simple interest" is used to describe features #1 and #2, but not #3.
Frequently Asked Questions
How do other types of loans handle early, late, extra, missed or partial payments?
In traditional loans and mortgages, interest is calculated on a monthly basis regardless of when the payments are received. Unless there is a specific reason to prorate interest based on date, the exact date of the payment is basically not part of the calculation of the interest.
An early payment is not applied until the next due date.
A traditional loan usually has a grace period for late payments. This works out well for the borrower if payments are made within the grace period, but the lender may not like it.
An extra payment of principal might be applied on the previous due date or the next due date, depending on when the payment was made within the period.
Missed or partial payments get confusing, and that is one of the main reasons I get questions from lenders who are used to a traditional loan. Should unpaid interest be added to the principal or not? It depends on the contracts and laws and how merciful the lender is. At least with a simple interest loan, the answer is well defined.
The variety of loans and methods for calculating interest are too diverse to explain in detail here. Also, laws often regulate how interest can be calculated as well as what fees can be charged by the lender for late and missed payments.
Why is the monthly payment in the Simple Interest Loan Calculator different than for a traditional loan?
It doesn't need to be. You can enter different monthly payments within the calculator to see how the payment affects the total interest and time to pay off.
The monthly payment is the amount that is expected to fully amortize (pay off the debt) after a certain number of months or years. The monthly payment estimated for a simple interest loan may differ by a small amount from the payment calculated using a traditional loan amortization schedule for one main reason: there are different numbers of days in each month (March has 31, April has 30, etc.)
The monthly payment for a Simple Interest Loan may sometimes be calculated exactly the same way as the monthly payment for a traditional loan (and that's okay).
Note: The monthly payment in this spreadsheet and most calculators is only the principal and interest portion. Your lender might add on insurance and property tax if they are handling that for you.
How do I enter a principal-only payment?
The spreadsheet currently does not allow you to enter a principal-only payment if there is any interest currently due. That is because interest is always paid before the principal and interest accrues on a daily basis. After all accrued interest has been paid, any extra payment is automatically applied to the principal.
References
The "Mortgage Professor" (Jack Guttentag) has some great articles that talk about Simple Interest Mortgages: "The Nomenclature of Simple Interest Mortgages", "What Are Simple Interest Mortgages?", "Amortizing a Simple Interest Mortgage", and How Does Simple Interest Work.
