Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Calculating Monthly Interest Charges

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Calculating Monthly Interest Charges.

Every month Judy needs to calculate an amount of interest to charge on her company's overdue accounts. They charge 18% annually, but Judy wants to compound the amount on a daily basis, based upon the customer's balance each day. She can't quite figure out how to come up with the monthly interest that should be charged to each customer's account.

A large part of the answer is going to depend on the nature of the data with which you are working. If your customers' balances change during the month because of payments and purchases, then the most understandable way to handle the situation is with a worksheet for each of your customers. Column A can have dates in it, column B can have purchases, column C payments, column D would be the interest charge, and column E would be the balance.

In this scenario you only need to place the amount of daily purchases and payments in columns B and C. Column D, which computes the daily interest, would have this formula:

=(E1+B2-C2)*0.18/365

This takes the previous day's balance, adds the purchases, subtracts the payments, and then calculates the interest on that amount. In this case, it is 18% (the annual interest rate) divided by the number of days in the year. In column E you would then calculate the balance for the new day, as follows:

=E1+B2-C2+D2

You can copy down the formulas in columns D and E and you will always know the balance for the account at the end of each day.

If the account only makes a single purchase or payment per month, then you can use the FV (future value) worksheet function to calculate the interest to be assessed at the time of each change in the account balance. You would do that using this formula:

=FV(0.18/365,days,0,start)

The only two variables you need to plug in here are the number of days between transactions (such as 23 days or 30 days or whatever it has been since there was a change in the balance) and the start balance at the beginning of that period. This start balance should be plugged in as a negative amount.

For instance, let's say that the account had a $1,000 balance at the beginning of the period. On the 25 of the month the account paid $250, so that was the day there was a change in the account's balance. As of the day before the change (as of the 24), the formula to compute the balance with interest would have been this:

=FV(0.18/365,24,0,-1000)

Of course, before you start charging interest to your customers you'll want to check to see if you are legally permitted to compound interest daily. Some governments may not permit you to do so. In that case you may be looking at calculating interest differently. Some companies forego compounded daily interest and simply charge a simple interest rate on the closing date for the month. This amount of interest is easy to compute, assuming your billing months correspond with calendar months:

=Balance * 0.18/12

There are a number of other interest-charging techniques that might be applied, as well. For instance, you might institute minimum monthly service charges (sometimes called carrying charges) or you might calculate interest based on a 360-day year or on a 13-month year.

To make sure you are calculating the charges appropriately, you'll want to check with both your accountant and your lawyer. (The latter because there could be verbiage in account agreements that stipulate how interest is to be charged and there could be laws that restrict some sorts of charges.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2817) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Calculating Monthly Interest Charges.

Related Tips:

Got the Time? Understanding the ins and outs of working with times and dates can be confusing. Remove the confusion--ExcelTips: Times and Dates is an invaluable resource for learning how best to work with times and dates. Check out ExcelTips: Times and Dates today!

 

Comments for this tip:

Cor van Nijkerk    20 May 2012, 05:48
I was paging back the tips I saved to find this one as I need it a little different approach. Basically this one comes close to my needs, but the interest percentage in my case is changing regular. How to solve this into this basic solution, thus a variable percentage valid for specific periods.

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.