Siegel, Here Are Your Articles for Monday, August 03, 2020
Is this email not displaying correctly?
View it in your browser .
Our Website Contact Us About Us Services Tutorials Acumatica
Share Save

Calculating the Total Interest for a Loan with Excel

 

In this article we'll see how to use as little as a single worksheet cell to calculate the total interest for a loan. To do so we'll use the CUMIPMT worksheet function—this translates to Cumulative Interest Payment. This can often be more efficient than building out a formal amortization table, and provides exactly the same result. The CUMIPMT function requires the following inputs:

• rate – This is the interest rate on the loan, which needs to match the basis for the term. For instance, a 30 year mortgage is often expressed as 360 months. In this case the interest rate needs to be divided by 12 to match the increment of the term.

• term – This is the length of the loan, typically expressed as years or months.

• pv – This abbreviation stands for present value, but is more commonly referred to as the principal of the loan. Money loses value over time, and so the interest rate on the loan compensates both for that loss of value as well as the lender's risk.

• start_period – This is the period number within the loan that we want to start calculating interest from.  You'll use 1 to calculate interest from the first month of the loan, or let's say 13 to calculate interest starting as of the second year of the loan.

• end_period – This too is a period number, not a date, for when we want to stop calculating interest. This number will be the same as the term argument if you want interest for the length of the entire loan, or could be 24 if you used 13 as the start_period to only calculate interest for a single year.

• type – Here we'll use a 0 (zero) so that loan payments are made at the end of each period, which is typical, or a 1 for payments made at the beginning of a period. In the case of a typical home mortgage or car payment, the first payment isn't due for a month after the loan closes, so in most cases you'll use a zero here.

By default the CUMIPMT function returns a negative number, as Excel considers interest to be an outflow. This clearly depends which side of the lending equation you're on. If you wish to change this to a positive number, simply insert a minus sign after the equal sign in the formula.

If it feels intimidating to type out this formula in the formula bar, click the fx button shown in the figure to display Excel's Function Wizard. Search for "total interest on a loan" and then double-click on CUMIMPT from the resulting list to access a fill-in-the-blank approach for pointing to the inputs. Just be sure to scroll down to fill in the Type argument, otherwise you'll encounter a frustrating error prompt. Finally, the yang to CUMIPMT's yin is CUMPRINC, which enables you to compute principal paid during a given period of a loan by using the same set of arguments.

 
Share Save

Your Comments

Siegel Solutions Inc
Siegel Solutions Inc
(781) 487-7000
info@siegelsolutions.com
144 Gould Street Suite 205
Needham, MA 02494
Friend Me on Facebook
Follow Me on Twitter
Connect with me on LinkedIn
Saved Articles
Comments and Feedback
Refer A Friend
Your Privacy
Our firm provides the information in this e-newsletter for general guidance only, and does not constitute the provision of legal advice, tax advice, accounting services, investment advice, or professional consulting of any kind. The information provided herein should not be used as a substitute for consultation with professional tax, accounting, legal, or other competent advisers. Before making any decision or taking any action, you should consult a professional adviser who has been provided with all pertinent facts relevant to your particular situation. Tax articles in this e-newsletter are not intended to be used, and cannot be used by any taxpayer, for the purpose of avoiding accuracy-related penalties that may be imposed on the taxpayer. The information is provided "as is," with no assurance or guarantee of completeness, accuracy, or timeliness of the information, and without warranty of any kind, express or implied, including but not limited to warranties of performance, merchantability, and fitness for a particular purpose.
Powered by
Copyright © IndustryNewsletters All rights reserved.

This email was sent to: jeff@siegelsolutions.com

Mailing address: 144 Gould Street Suite 205, Needham, MA 02494