0

I am terrible at finance and math so this really hurts my brain but I want to work out the compounding monthly interest for an initial amount over x days.

For example I have $1500 over 95 days at 10%. Ignore the numbers so please just work with this.

My google doc spreadsheet formula is

=F16*(((1+(K15/360))^(H16-G16))-1)

f16 = $1500 k15 = 1.10471 (10% as annual interest? Got that from somewhere) h16 = 5/30/2019 g16 = 9/2/2019 h16 - g16 = 95 days

Which gets me: $506.80.

Is this correct or did I go wrong somewhere?

Thanks

3

Your formula gives you daily compounding, assuming the annual interest rate was calculated on 360 days (a slightly shorter ‘year’ than a natural year, but not unheard of in the finance industry).

If K15 is your ‘annual’ interest rate, K15/360 is your daily interest rate. (If you have a 10% rate, K15 should be 0.1, not 1.1. The extra 1 makes the interest rate 110%, which suggests runaway inflation or a mathematical oversight.)

To get monthly compounding, you’ll need to work out how many months the duration lasts. Natural months vary from 28 to 31 days, but if you’re using 360-day years, you might also be breaking that down to twelve 30-day months.

Three months is then 90 days, and you have 5 days left over from your 95 days. You’ll need to decide whether to apply any interest to those days, or whether those 5 days simply attract no interest. That is, does interest accrue daily and compound monthly? If so, keep track of daily interest and add it to the principal at the end of each month. It is simple interest throughout the month, then the principal goes up at the start of the first day of the next month by the amount of interest earned.

If you simply forfeit the part-month’s interest, change the formula so that you’re using monthly interest: use K15 * 30 / 360 instead of just K15/360. Also change the duration H16-G16 from number of days to number of months: (H16-G16)/30, rounded down to remove the foregone 5-days of interest. The rest of the formula should be fine as is, but note my comment above that K15 needs to be the interest rate, not 1+rate.

If instead you are working on an accrue-daily, compound-monthly scheme, you’ll need a different formula altogether.

Either way, to your question of whether you went wrong somewhere, the answer is that you went wrong at calculating K15 and at using a formula for daily compounding instead of monthly compounding.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.