Daily/Weekly/Monthly Compound Interest Loan Calculator in Excel

We’ve got some good news for you!

Recently we just released a new version of our Compound Interest Loan Calculator in Excel.

It’s often called “Amortization Schedule with Principal and Interest Amounts” which is basically the same thing as simply “Loan Calculator”.

The enhancements are:

1. Added Compounding Interest Frequency (daily, weekly, fortnightly and monthly)
2. Possibility to change Repayment Dates
3. Option to update Repayment Amount
4. Password unprotected worksheet (Paid version only)

The above changes make this Excel Calculator even better and far more flexible!

Now we would like to tell you a bit more about what these enhancements will mean for you.

1. Interest Compounding Frequency (daily, weekly, fortnightly and monthly)

As you probably know, Payment Frequency and Interest Compounding frequency are not the same things.

Payment Frequency means how often you make your repayments.

Interest Compounding Frequency means how often your Bank calculates and ads Interest to your Loan Balance. It’s not the same thing if they do it let’s say monthly or daily. With mortgages the difference can be few thousands of dollars over many years.

Here is the logic:

Let’s say your Loan interest is 6% per year (p.a.). If you make monthly repayments and also your Interest is compounded monthly then the total interest paid per year will be slightly higher than 6%.

Why?

Because the banks like to make money and they do so even by not disclosing the whole story (or disclosing it only in the small print).

Let’s have a look how it works.

If your yearly Interest rate is 6.00% then your monthly rate is 6/12 = 0.5%

The first month the bank will calculate Interest as 0.5% of your outstanding Loan Balance and add it to your Loan Balance. The second month the bank will do the same. But this time it will also be the interest on top of interest from the previous month.

Are you beginning to see the picture?

If they do it every month then your effective interest rate per year will be like this:

Effective Interest rate = (1 + 0.06/12)^12 – 1 = 0.0617 = 6.1679%

So in fact you are not paying 6% interest rate but 6.17%. That’s why the banks make so much money!

And what’s the worst part?

Well, the true is that most banks don’t compound the interest monthly. They push it even more. They do it daily! Buggers…

So, if they compound it daily, let’s have a look what the result is now:

Effective Interest Rate = (1 + 0.06/365)^365 – 1 = 6.1831%

You see, the difference between monthly and daily compounding may not look big, but if your loan is couple of hundred thousand dollars then over time the amount can be significant.

The problem is even more apparent with let’s say credit cards because the base interest rate is usually much higher than mortgages.

If your credit card yearly interest rate is 20% then daily compounding will push it to 22.13%.

The moral of the story:

Be aware of the compounding interest and ask your bank upfront how they calculate it.

If the bank employee plays dumb or pretend he or she doesn’t know what you are talking about, ask their manager. Don’t sign up for any loan before you know exactly what method they use.

To help you calculate the exact Interest Amount for each method we added an option to choose monthly, fortnightly, weekly and daily interest compounding rate or frequency.

Now, let’s have a look at other adjustments we made to our Loan Amortization Schedule Calculator.

2. Possibility to change Repayment Dates

It’s nice to calculate ideal Amortization Schedule and assume that you pay your repayments exactly on due day, every time. The reality is that it’s often not the case.

Sometime you miss payment for just couple of days because you forgot about it or had insufficient funds in your bank account.

As you probably know, the banks are unforgiving. If that happens, the bank will simply add Interest to your Loan Balance for every single day overdue.

After some time the actual Loan balance will differ significantly from the original amortization schedule.

To help you modelling your loan based on real life scenarios, we added possibility to adjust or change actual Repayment Date. This way you will be able to predict actual Loan Balance more accurately.

One thing to remember:

Once you change the Repayment Date, the next Repayment will also be adjusted to align with your Repayment Frequency. To get it back to your original schedule, adjust the next Payment Date back to what it ought to be.

3. Option to update Repayment Amount

Similarly as missing your repayments or paying a bit late, you can also experience paying different amount.

If this happens, simply overwrite the Repayment amount for that particular period.

You can also use the field Extra Payment for that purpose. It’s possible to add both positive and negative amounts into this field.

Our previous version of the calculator was password protected. The main reason was to avoid accidentally deleting or changing formulas. This can make the calculator to work incorrectly.

However, we found out that this safeguard put in place was restricting some of you. We have therefore decided to remove all protection.

You can now freely change everything in the calculator. As a safety measure we recommend following:

After downloading the calculator; save the original file somewhere on your PC. Then create a copy of the calculator and work with the copy. If you accidentally screw something, simply make a new fresh copy from the original file and start again.

We hope you will enjoy the new version of our Loan Amortization Schedule Calculator!

If you have any questions or suggestions to improve it even further, we would love to hear from you! Send us an email or leave comment under this article.

Posted on: January 15, 2019
Categories: Articles
20 Responses to Daily/Weekly/Monthly Compound Interest Loan Calculator in Excel
1. Downloaded the free version of this, only thing it will let me edit is the frequency of payment (e. monthly) pretty annoying!!!

• There must be something wrong with your PC. Normally, in FREE version you can edit all these fields:

Loan / Mortgage Amount
Repayment Frequency
Compounding Frequency
Repayment Amount
Interest Rate (as % p.a.)
Loan Start Date

I have double-checked it and it works. The only limitation in FREE version is that it will calculate max 30 repayment periods. For more you need FULL version.

I would recommend checking the calculator on some different PC.

• Strange, I’ve just downloaded this and I also find that the fields as mentioned above cannot be edited and I am using Excel 2016

• You have downloaded a demo version. If you want a fully editable and functional spreadsheet, you need to purchase it.

Perhaps you should change it to read DEMO VERSIOn. As it is – it is misleading and one thinks its an oeprational but free version – when in fact it is only a non functional demo version!

• Thanks for reminding us. We have updated the wording so it is more clear now.

2. I need to have a loan calculator that will preform the following.
1) Will amortize the loan on a daily basis
2)Allow flexibility to make payments on a non-regular basis
3) Will deduct the payments when made and the continue to amortize the balance
4) Will allow me to print out the balance after the payment is made
5) Will do this for at least 720 days

• I believe this calculator does all of that. However, I recommend to download a free sample version and test it.

3. I need to pay a specified home loan amount on a specified date. I am being charged daily interest and my loan repayment is weekly or whatever I decide. I need a repayment schedule.
What do you suggest

• What do you mean by “specified home loan amount on a specified date”? Does your home loan have a “balloon payment” at the end?

4. My copy of excel refuses to op your file

• It is designed for Excel 2007 and 2010 for Windows. Should also work in later versions as well, however it may not be compatible with Excel for Apple Macs…

5. “Adjustable payment dates ie. late payment.” How about arbitrary extra early partial and full payment in between scheduled payments with date of extra payment calculated when paid?

• You can add any payment date and the calculator will adjust the schedule accordingly

6. Can I do a schedule without compounding?

• No. This calculator must have some interest compounding frequency entered.

If you don’t want to use interest compounding frequency, you can overwrite the formula in field G18 to:

=((D18-D17)/365)*(\$E\$11/100)*E17

If you are using an upgraded version of the calculator then overwrite field G20 to:

=((D20-D19)/365)*(\$E\$13/100)*E19

And then copy the formula to the rows below.

7. Will the paid version allow me to change interest rates at a certain period of the loan? Can it also change payment amount needed to be paid off in x number of payments?

• No, this calculator has just one rate for the entire loan term. Payment amount can be changed (manually overwritten within the schedule and the loan balance will adjust accordingly).

8. Do you have one that compounds quarterly?

• Unfortunatelly no, only daily, weekly, fortnightly and monthly compounding frequency.