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

# Calculating Monthly Interest Charges

by Allen Wyatt
(last updated August 21, 2018)

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 (10344) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Calculating Monthly Interest Charges.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Many businesses need to know when the last business day of the month occurs. This tip discusses several ways you can ...

Discover More

Conditional Formats that Distinguish Blanks and Zeroes

Conditional formatting is a great tool. You may need to use this tool to tell the difference between cells that are empty ...

Discover More

Creating Custom Document Properties

Word allows you to keep track of any number of custom properties about a document. Here's how to create those properties ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

##### More ExcelTips (ribbon)

Only Showing the Maximum of Multiple Iterations

When you recalculate a worksheet, you can determine the maximum of a range of values. Over time, as those values change, ...

Discover More

When working with large amounts of data, it is a good idea to make sure that the data all consistently follows a pattern. ...

Discover More

Returning the Smallest Non-Zero Value

In a series of values you may need to know the smallest value that isn't a zero. There is no built-in function to do ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is eight minus 8?

2019-03-30 12:53:55

MIchael Armstrong

Excel aside, I've found that often my calculation(s) don't agree with those of my insurers, mortgage holders, etc., because of rounding effects, and other quirks, like always using 360-day years. It may take a while, but ask for details of computation, and often you can figure out how to calculate and get the same numbers they do. I've found such errors in one widely-used income tax program because of detailed IRS rules for calculation that weren't followed.

2019-03-29 09:35:00

Thomas David Caughman, Jr.

good morning.
I have a spreadsheet with all my debts listed, including balance, apr, min payment, etc.
Is there a way to make the sheet automatically update the balance CORRECTLY by my inputting a payment? for example, if I pay \$50 and simply deduct it from the existing balance, this will not yield the correct new balance, because it does not include the interest.

2018-10-24 19:53:15

Peter Atherton

Diane J

Try the formulas shown below. The schedule shows the first 10 days you could copy it down.

HTH

(see Figure 1 below)

Figure 1. Interest Calculator ans Schedule

2018-10-23 15:37:18

Diane J Bowman

I am really in need to improving and increasing my excel skills however I am in a dire need for someone to help me set up an excel sheet to calculate an amount owed to me (\$ 2, 6256.74)that was never paid since Jan. 18, 2016 and include an interest rate of 5% compounded daily/monthly with aa \$38.00 late fee after 30 days with 5 day grace period before being applied.

I wrote the letter for the claim and have been having a hard time trying to compute because no money has been paid all this time and I need to get this letter out so i can either get a settlement from them after I give them the invoice, and proceed to court to file a claim to recoup the money. I am homeless and disabled temporarily and really could use the money.

If you would be willing to give me a dollar amount for your help I would gladly paid for it so I can just finish with this and work on my skills after.

Thank you. Please let me know either way.
Diane Bowman

2018-08-22 03:56:24

Mike

Tara, whilst I take your point, the problem suggested to me that the APR should be 18%, not 19.72%. It's seems probable that 'Judy' could easily have come up with your formula without assistance. Simply dividing by 365 does not produce an 'annual rate' of 18%.

2018-08-21 16:12:59

Tara

Mike, an ending balance of \$118.00 would be an example of simple interest, not interest compounded daily.

2018-08-21 13:31:29

Mike

This formula would not appear to be correct. If a 100.00 purchase was made on day 1, then if no further purchases or payments were made one would expect the outstanding balance on day 366 to be 118.00, not 119.72.

I think the formula in D2 should be "=(E1+B2-C2)*(1.18^(1/365)-1)"

##### This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."