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)

Improve this question

2 Answers 2

Reset to default
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

Improve this answer
1
  • 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, 2015 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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .