2

I invest in a few medium to low risk accounts online (peer to peer loans and buy to let property crowdfunding).

I make regular deposits into these accounts, usually when the monthly salary comes in, but the time period between my deposits and the amount changes.

I know how to work out the effective APR from interest earned on a single deposit over a time span, for example, taking C (capital deposit), P (profit from interest), and T (time span in days) it would be:

APR = ((P/C + 1)^(365 / T) - 1) * 100

But if I then extend this to be over two time periods, taking: C1 and C2 (starting amounts), T1 and T2 (timespans in days), P (total interest earned), and I (daily interest rate for simplicity)

P = (C1 * (I^T1 - 1)) + (C2 * (I^T2 - 1))

Is there a direct method to get the average interest rate (I) over the two periods, knowing all other variables?

I have attempted to create an iterative formula by rearranging to get one of the I's on one side:

I = (((P - C2 * (I^T2 - 1)) / C1) + 1) ^ (1/T1)

Taking the initial value of I as 1. But this equation diverges when I try to iterate it.

Is there either an iterative approach which converges or a direct approach?

(Money stack exchange does not have MathJax so I will repost these formulae as images once I have enough rep)

3

If I understand your question correctly I think the easiest solution would be to use the XIRR formula in Excel. You can have a column of dates, and a column of cash flows, and it will calculate the rate for you.

Description

Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function.

https://support.office.com/en-sg/article/XIRR-function-de1242ec-6477-445b-b11b-a303ad9adc9d

  • Yes, that's what I use to calculate my effective annual return: guzintas + dates, guzoutas + dates, and the current value + today's date. – Peter K. Sep 10 '15 at 18:45
0

I wanted to do this in my own program rather than having to use a 3rd party application such as Excel. In the end the approach I found was best was the following:

  1. Set variable TooLow to be an interest rate that is obviously too low, ie. zero.
  2. Set variable TooHigh to be an interest rate that is obviously too high, ie. 10000% APR (assuming no payday loans here).
  3. Set Mid = (TooHigh + TooLow) / 2.
  4. Calculate how much interest you would have got assuming that the rate is Mid.
  5. If the interest calculated is less than the actual interest you got then set TooLow = Mid, If more then set TooHigh = Mid.
  6. If you are not close enough then go to step 3 and repeat.

This method is a binary search algorithm and therefore is quite fast. It doesn't take many iterations to get accurate to a few decimal places.

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.