null

HOW TO CALCULATE THE RETURNS
ON YOUR INVESTMENTS USING EXCEL

This is fairly easy to do for investments of lump sums.  It is not quite so easy for investments where you contribute on a regular ongoing basis.

LUMP SUM INVESTMENTS

Let’s take an example of a lump sum investment of R250 000, made 25 years ago.
Please download our example Excel Spreadsheet.

See the Downloaded spreadsheet named: “Investment Return Calculations”.  Go to the first tab which is titled R1 Capital.

Enter numbers in cells B1, B2 and B3 to guide you.  The percentage in B3 is significant but you can start by entering any percentage you like as it going to change.  In this case I have used 7.00% to start with.

I have used column E for the calculation and typed =B1 in cell E1 which puts R250 000 in that cell.

In cell E2 type =E1 + (1+B3) and press enter.  Put your cursor on Cell B3 and pushF4.  So the equation in cell E2 now looks like this =E1+(1+B$3$).  This will then increase that capital figure by the percentage in B3 for every year.

Copy this cell down until cell E26 and stop.  The first cell of that column E1 is effectively the investment at the beginning of year 1.  The value in cell E2 will be the value at the end of that year.  All the other values in that column will represent the values at year ends.  So the amount in cell E26 will be the value at the end of the 25th year.

The amount in cell E26 will equal the compounded growth of that capital at the assumed rate of 7% per year over the 25 years.  That value will be R1 356 858.

Now to work out the rate at which your money actually grew.  We start with the actual value that your fund is worth and enter that in cell B5.  Then, in cell B6 type =E26 which will transfer the end value of the calculation that you did in column E to cell B6.

In cell B7 type = B5 – B6 and the difference will be shown.

Click on the “Data” tab at the top of the ribbon on Excel;
Then click on the “What if icon” and click on “Goal Seek”;
This dialog box will appear;

In the space marked “Set cell” type in B7 and tab to “To value”.  Cell B7 shows the difference between the value you have taken from your statement (cell B5) and the value in cell E26.  Set that to 0 (Zero).

Then you go to “By changing cell” and enter B3.

Press OK twice and you will see that the percentage in cell B3 will reflect the average annual investment return rate by which your capital actually grew over the 25 years.

As mentioned above the R250 000 invested for 25 years at a guessed return of 7% pa will yield R1 356 858 which will be shown in cell B6.  If we assume that the amount on your statement is R1 200 000 then you enter that in cell B5 and the difference will be R156 858.  The Goal Seek feature will work out an investment return that will reduce that difference to zero.

The real annual average compound percentage return on R250 000 invested 25 years ago that is now worth R1 200 000 is 6.48 % per year.  That’s your investment return.

null

MONTHLY PAID INVESTMENTS

Let’s take the example of a monthly contribution to savings of R500 per month for a period of 10 years (120 months).

See the downloaded spreadsheet titled: “Investment Return Calculations”.  Go to the second tab which is named R1 Per Period.

Enter numbers in cells B1, B2, B3 and B4 to guide you.  The percentage in B4 is significant but you can start by entering any percentage you like as it will change.  In this case I have used 7.00% to start with.

I have used columns F and G for the calculation and typed =B1 in cell F1 which puts R500 in that cell as a contribution for the first month.

In cell F2 I type =F1 + (1+B3) which brings in the first R500 contribution in the first month, and press enter.  I then copy cell F2 down to cell F120 which shows the payment each month.  I have made provision for annual payment increases with a possible increase percentage for each year in Cell B3.

In column F I have taken the first cell of each new year (or month 1) and entered the formula =F(cell above) *(1+B$3$) to allow for one payment increase per year.  I will do the calculation example based on no annual contribution increases but if you enter the annual payment increase percentage it will give you the capacity to calculate your end results properly.  That takes care of the payments coming in.

In column G we start by entering =F1 in cell G1.  This brings the first monthly payment into that column.  Cell G2 brings in the next monthly payment by starting with the instruction =F2.  To this is added the previous monthly contribution plus the interest earned in the first month.  So that cell G2 has =F2 + (G1 + (1+((B$4$)/12))) which provides a value for the 2nd payment plus the first payment and its growth.

This cell G2 can then be copied down to cell G120 which holds the final value for 10 years (120 months).  This total is brought to cell B7 by typing =G120 in the cell.  The actual amount of R98 000 (for the example) that appears on the statement is entered in cell B6 and the difference of R11 457.60 appears in cell B8.

Now to work out the rate at which your money actually grew.  You start by putting the cursor on cell B8 and then you click on the Data tab at the top of the ribbon on Excel;

Then click on the “What if icon” and click on “Goal Seek”;

This dialog box will appear;

In the space marked “Set cell” type in B8 and tab to “To value”.  B8 shows the difference between the value you have taken from your statement (cell B6) and the value in cell G120.  Set “To value” to 0 (Zero).

Then you go to “By changing cell” and enter B4.

Press OK twice and you will see that the percentage in cell B43 will reflect the average annual investment return rate by which your capital actually grew over the 10 years.

As mentioned above the R500 000 invested for 10 years at a guessed return of 7% pa will yield R86 542 which will be shown in cell B7.  If we assume that the amount on your statement is R98 000 then you enter that in cell B6 and the difference will be R11 457.60.  The Goal Seek feature will work out what investment return will reduce that difference to zero.

The real annual average compound percentage return on R500 per month for 10 years, now worth R98 000 is 9.23 % per year.  That’s your investment return for the period.

HOUSING BOND CALCULATOR

Download our easy to use Excel Bond Calculator.

Let Us Contact You

Or You Can Contact Us