Get Interest Rate via Formula (not goal seek) from the total earning

[ylbochner]

Hi,

I have an excel sheet with an investment amount per year and the end result of earnings.
Example:
Year 1: 20,000
Year 2: 23,000
Year 3: 15,000
Total Cash Value including compound interest after year 3 is 65,000.

I would like to know what interest rate was used in order to get to 65,000. I know how to set it up in order to use goal seek, however what I really want is a formula that can do this instead of goal seek. any suggestions?

Thank you

[MrShorty]

You will probably either need to explain your financial model to use or explain to us exactly how you set this up when you use goal seek.

Excel has functions for 3 financial models programmed in:
PV() family of functions (https://support.microsoft.com/en-us/...1-da16e8168cbd ) which includes the RATE() function to find the interest rate.
the NPV() family of functions (https://support.microsoft.com/en-us/...rs=en-us&ad=us ) which includes the IRR() function to find the interest rate.
the XNPV() family (https://support.microsoft.com/en-us/...b-d67c16b664b7 ) which includes the XIRR() function to find the interest rate.

For example, I can enter =IRR({20,23,15,-65}) into a cell and get something near 5.5%.

If your desired financial model fits into one of those three models, then you can use the built in interest rate function for that model. If your desired financial model does not fit into one of those three, then you will need to build your own interest rate function (or somehow transform your model into one of these three). From what I see, interest rate often requires numerical algorithms (goal seek/Solver type algorithms) that make this programming exercise less straightforward.

Help us understand your financial model, and we should be able to help you program the interest rate calculation into Excel.

[ylbochner]

Thank you for responding.

I attached an excel sheet which I am working on.
In column "E" I would like to calculate the compound interest rate used to get to the number in column "D".
For example, in year 17 the cash value is 640,142 (cell D17). In E17 I would like the annual interest rate per year used in order to get to the number of 640,142.

In columns F-H is were I set it up to use with goal seek. but since I want this for every line, a formula is needed.

[ylbochner]

Looks like the IRR is the correct formula to use. Is their a way to use it referencing cells instead of actual numbers?

IRR({A1,A2,A3,-B3})
vs
IRR({20,23,15,-65})

[MrShorty]

The IRR() function is designed to receive a simple 1D array/vector as input, so =IRR(A1:A4) or =IRR({20,23,15,-65}). In your case, the inputs are not in a simple 1D vector, so you need to do something to get the inputs into a vector. I am not good at this kind of programming, but I have seen this done before:

Ace_XL seems to have working expressions for getting a non-vector range into a vector that IRR() can receive: https://www.excelforum.com/excel-for...ml#post5262243
XOR LX and JeteMc play around with something similar: https://www.excelforum.com/excel-for...ml#post5139920
Joeu puts together something for both the XIRR() and IRR() functions here: https://www.excelforum.com/excel-for...ml#post5139920

If you can piece together how these users get from non-contiguous ranges to a contiguous vector inside of their XIRR() and IRR() formulas, you should be able to piece together something similar for your scenario. Let us know if you get stuck.

[joeu2004]

In column "E" I would like to calculate the compound interest rate used to get to the number in column "D".

See the attached file.

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into E3, then copy down the column:

=IRR(IF(ROW($C$3:C4)=ROW(C4), -D3, $C$3:C4))

As a proof-of-concept, array-enter the following formula into I3, then copy down the column and compare with G1:

=IRR(IF(ROW($F$3:F4)=ROW(F4), -H3, $F$3:F4))

The payment in column C (F) is presumed to be at the beginning of the year, whereas the cash valuation in column D (H) is presumed to be at the end of the year, which is equivalent (closely enough) to the beginning of the next year.