I want to discuss an investment return calculator that can be used for variable monthly contributions or pay outs.
The normal formulas that takes the final amount and total contributions does not work when your cash flow into or out of the investment changes.
Now to calculate investment growth, we will be using the IRR formula. The IRR calculator is an Internal Rate of Return calculator.
This formula can be used to calculate most type of investments, and is exciting to see it work.
Lets's get to the guts of the formula, or rather see how it works.
Lets say for example you put money into an investment, different amounts each month.
Then after some years you get the money from the investment.
Now you put in those amounts, and get the results.
The IRR formula works with cash flow.
So money you put into the investment is negative. The money you get out is positive.
The formula also takes into account the time each amount was in the investment.
For example:
You put in $10 each end of month into some fund for 20 months.
After 20 months at month 21 you get $300 out.
Lets calculate:
Using a program like EXCEL we put in the fields as below, and get the answer.
As you can see I put the formula and get the answer in Cell A22, but it could be in any other open block.
The formula is shown at the top right: IRR(A1:A21)*12
IRR works out the annual return. Because we put in monthly amounts we have to multiply the result by 12. So the formula calculates the IRR for cells A1 to A21, which is the pay out amount of $300.
If you use other formulas, it will use the amount you pay in as $200 ($10 x 20 months). The amount you get out is $300. So those formulas will calculate growth as (300-200) divide by 200, so you get 50% growth. IRR get 45% growth.
Most other formulas doesn't take the time the money was paid in. So the first money was in this investment for 20 months, and last money for only 1 month. From there comes the difference.
Now you have a powerful tool. With it you can work out the growth on your house. You simply put in the amount of money you paid each month including repairs and refurbishments. Then when you sell it, you put in the positive amount you get out, that is after deducting all expenses.
You can calculate growth on a retirement policy for example, where you pay an increased contribution each year. You put in the money you will pay in each year, and in the final cell the amount they promise you. By the way, I did exactly that and cancelled my retirement policy the day after that. Check what results you get and decide for yourself.
As another example, you can use the formula to calculate if it is a good investment to put up solar panels to replace your electricity. You put in the cost to build the system, then put the cost you will save on electricity each month as income / positive cash flow. Then put in battery replacement cost at year 10 for example. The electricity cost you save will increase with time. From there you can make an informed decision. Below is a imaginary example:
As you can see we get a return of negative 1% on this investment. From there you can make an informed decision of how big a system you want to put in, etc.
The point is to always do the calculations before you make decisions, or else you will make emotional decisions. This formula will be of a lot of help in most cases.
Be sure to watch the video below for more.
