We noticed that there were a number of requests with regards an Internal Rate of Return calculator. We have provided a free excel IRR Calculator that works on Excel 2003 and Excel 2007 and allows a user to capture the date of a cash flow, the amount of in or outflow and will determine the corrrect IRR. We have also provided a short video tutorial on how to use the IRR calculator.

You can download the excel file by clicking here

You can watch the video clip by clicking here

Excel offers an array of functions useful in financial decision modeling. One such a function which can be used for decision modeling is the IRR function. The IRR function can be used for the calculation of an internal rate of return for a specific series of values. To understand the IRR function it is necessary to have a closer look of what the IRR or Internal Rate of Return is. It can be described as the interest rate accumulated on a specific investment which consists of payments as well the incoming money, which are present over the corresponding regular periods.

Payments are entered in the values as negative values. The income is also entered in the values for the function, but enter the income as positive values.

Decision modeling scenario

If you for instance, invest in an enterprise which makes payments to you every year for a period of four years in total and you plan to invest an amount of for instance, R60 000 of which you have an expectation to receive back in the first year an amount of R10 000, R17 000 in the second year and in the third year R24 000 while R30 000 for the last year, you will enter the above as positive values.

The amount paid out will be entered as a negative value. The amount of R60 000 will thus be a negative value. The R60 000 will thus be entered as –60 000 in for instance, cell A4 while the amount of R10 000 will be entered as 10 000 in A5 and so forth.

The relevant formula will thus be:

=IRR(A4:A8).

One of the most frequently asked questions at Excel forums regarding spreadsheets for business decisions is that of how to calculate the CAGR (Compound Annual Growth Rate). CAGR is also known as the smoothed rate since it is used to calculate the growth of an investment over a specific period annually compounded as if the investment has grown steadily every year to the final year.

If we take a scenario where you have two amounts with two specific dates such as 2002 $1000 and 2007 $5000 and you want to get the CAGR from the available information then the formula is as follows:

You will take the final number and then divide it by $1000 which is the other number available raised to the power of one over the total of annual periods. Then subtract one from the result of the above calculation and this will give you the compound annual growth rate. It is thus:

=cagr(start date cell, end date cell, years).

Or
=(1+rate)^years

rate=…..
years=…..
=(1+.1)^….

Another frequently asked question regarding CAGR for business decisions is what tool to use in Excel for the CAGR. To do the calculation you can make use of XIRR or RATE in the Add-ins. If you are using Excel 2003, you can click Tools, then Add-ins and follow the instructions for set-up. Select the XIRR or RATE function for this purpose. View our free online video and printable script for using the RATE function effectively for purpose of calculating the CAGR.