2

I am a programmer knowing next to nothing to interest calculation and I have to determine a formula to calculate interest on a daily basis compounded monthly. The main problem here is that the interest rate may change at any point in time (we are based on bank prime, which is constantly updated).

In one question on this site, I found the following formula: I= P(1+r/12)^n * (1+(r/360*d))-P which works fine when the calculation starts the first day of the month and the rate never changes. But, because of rate changes, we have to split the calculation into numerous segments, one for each rate. With a partial month at the start and at the end. But splitting the calculation will not compound interest for the part of the month before the partial first month.

So, actually, the question is: how do I calculate interest on a daily basis compounded monthly over a period of time during which the rate may change at any point in time?

I am totally confused as to how I can get an accurate calculation result.

UPDATE: After discussing the issue here, I discovered that the rate will never change for a given loan. However, it may still start on any day during the month. How do I compound this first month?

Brythan
  • 20,968
  • 6
  • 53
  • 67
Réal Forté
  • 21
  • 3
  • Is the rate given as APY or APR? – Acccumulation Dec 08 '17 at 15:32
  • 4
    I'm voting to close this question as off-topic because it is either homework or something that the OP needs to create a work product, that is, not about personal finance. – Dilip Sarwate Dec 08 '17 at 15:41
  • 2
    If the interest rate can truly change daily, then it may be more trouble than it's worth to come up with a formula for each period. It might be easier just to iterate on each day, determine the interest rate for that day, and accrue the interest (adding it to the principal balance) when you hit an accrual period. – D Stanley Dec 08 '17 at 15:43
  • You can price the loan off a futures yield curve for the prime rate relatively easily. https://quant.stackexchange.com/ can help but you'll need to do a lot of research into yield curve creation before they will consider the question on topic... – MD-Tech Dec 08 '17 at 16:20

2 Answers2

0

How do I compound this first month?

The accrued interest for the first month will just be the effective daily interest rate from the annual rate r, which will either be r/360, r/365, or r/(actual number of days in the year), depending on the terms of the loan.

The amount of accrued interest in the first period will be 

(starting balance) * (daily rate) * (number of days in initial period)

After that first period, the accrued interest will be added to the initial balance so it can be compounded.

D Stanley
  • 133,791
  • 19
  • 313
  • 372
0

Here is a formula you can use:

pv is the present value of the loan
c is the periodic repayment amount
r is the periodic interest rate
n is the number of periods
x is the fraction of a period by which the first period is extended

E.g. £1000 loan at 10% effective annual interest repaid on month ends. First period begins January 21st, last payment is on December 31st of the same year, so 12 repayments.

The first period extension is -21/31 shortening the January period to 10 days.

x = -21/31

pv = 1000
n = 12
r = (1 + 0.10)^(1/12) - 1

pv = (c (1 + r)^(-n - x) (-1 + (1 + r)^n))/r

∴ c = (pv r (1 + r)^(n + x))/(-1 + (1 + r)^n)

∴ c = 87.2449
Chris Degnen
  • 9,797
  • 1
  • 20
  • 36