Excel Loan Calculator with Daily/Weekly/Monthly Compound Interest

We’ve got some good news for you!

Today we have released a new version of our Loan Amortization Schedule 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 (Premium version only)

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

Free Loan Amortization Schedule Calculator with Adjustable Interest Compounding FrequencyTo download Free version: click here or on the Excel icon.

To buy Premium version: click here.

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.

4. Password unprotected worksheet (Premium version only)

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.


Comments

Excel Loan Calculator with Daily/Weekly/Monthly Compound Interest — 2 Comments

    • 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>