Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

OfficeFix

Excel Financial Functions–Calculating IRR for Irregular Cashflows

This week, in the final article of this series, guest blogger Nick Williams discusses calculating the Internal Rate of Return for an irregular cashflow. Nick is an Access tutor based in the U.K.

XIRR – Internal Rate Of Return

XIRR function returns the value for an Internal Rate Of Return for a supplied series of cash flows, set of values. The key difference between IRR and XIRR is that XIRR can deal with cashflows that aren’t evenly spaced. Because it can deal with cashflows that aren’t evenly spaced you need to tell is both the series of cashflows and also the dates of each cashflow.

The syntax of the XIRR function in Excel is XIRR (values, dates, [guess])

values is a required parameter. It is an array or reference to a set of cells that contain the cash flow values.

dates is another required parameter, it is a series of dates. You need to have a date that corresponds to each cash flow for the calculation to run. The first date is the date of the investment/loan, and subsequent dates refer to income values.

guess is an optional parameter, and works as it does for IRR. Again, if this parameter is omitted, it is set to 0.1 (10%) by default

Continuing with our previous example, let’s introduce dates into our cash flow set.

We now have dates for the initial investment, and irregular dates of actual and/or forecasted income over the seven years of the project.

As we would expect, as the returns come sooner on this project the return on it has gone up, when compared with our previous calculation.

If we entered dates that were exactly one year apart for each of the cashflows this would give an answer of 1.82%, the same answer as using the IRR function.

Again continuing with our previous example, lets we add more expected income and periods. Again as the returns come sooner the return on the project has increased when compared to have all of the cashflows spaced exactly one year apart.

WHAT TO DO IF YOUR CALCULATION RETURNS #num!

Microsoft Excel uses an iterative technique for calculating IRR AND XIRR starting from guess. If XIRR can’t find a result that works after 100 tries/iterations, the #NUM! error value is returned. If this happens, or if the result is not what you expected to see, try again with setting a different value for guess.

#NUM! error can also appear if:

1. there isn’t at least one positive and at least one negative value in the list of cashflows

2. any of the specified dates precede the first specified date

3. the specified dates and values arrays have different lengths

XIRR can also return a #VALUE! error if it cannot not recognize any of the specified dates as dates (e.g. you have the value of 20 among your dates array)

Excel Financial Functions–Calculating IRR

This week, guest blogger Nick Williams discusses calculating the Internal Rate of Return for a regular cashflow. Nick is an Access tutor based in the U.K.

IRR – Internal Rate Of Return

IRR returns an interest rate. It is the interest rate that would need to be applied to all of the cashflows in the calculation to give an NPV of zero for the project (see above). In short it tells you the return on the money invested in a project and is often used as an investment decision tool. It is designed to work with a set of regularly spaced cashflows only.

IRR is the same as interest rate of savings or loan, however there is it applied to a very straight forward situation.

The syntax of the IRR function in Excel is IRR(values, [guess])

values is a required parameter. It is an array or reference to a set of cells that contain the cash flow values for the project.

guess is an optional parameter. It is a number that you are guessing is close to the result of IRR. If this parameter is omitted, it is set to 0.1 (10%) by default.

Continuing with our example of above we can see the function in use below.

Intuitively we know that that is right. Above we discovered that the project had a negative NPV if we applied a 3% discount rate. Therefore we knew that the discount rate to get an NPV of zero had to be below 3%.

Again, as above, lets add two more years of income and see what happens to the result. We got a positive NPV using a 3% rate before so we know that the answer will be above 3% before we start.

Note: Microsoft Excel uses an iterative technique for calculating IRR starting from guess. If IRR can’t find a result that works after 20 tries/iterations, the #NUM! error value is returned. If this happens, or if the result is not what you expected to see, try again with setting a different value for guess.

Excel Financial Functions–Calculating NPV

We’d like to welcome back guest blogger Nick Williams. This week and for the next two weeks, Nick will be publishing a series of Excel financial calculation tutorials. Nick is an Access tutor based in the U.K.

One area where Excel is used extensively is in financial forecasting and modeling. It contains lots of financial formulas which can save hours of time.

As ever the trick is to give Excel the right data in the right way … and then let it do the work for you.

In this article we’re going to have a quick look at three of the basic formulas that Excel has in its kit bag IRR, XIRR and NPV.

NPV – Net Present Value

The NPV returns a value amount. You insert all of the forecast cashflows (both investment and return) relating to a project and also your cost of capital. NPV will then tell you if the project will return a ‘profit’. Profit in this context is that it returns more than your cost of capital for the period that your capital is invested. Put the other way round the value of all of the projects future cashflows, today, is greater than nought.

The basis of the calculation is that a pound now is worth more than a pound in future. Your cost of capital (or discount rate) is the difference in value between a pound now and a pound in a year’s time.

To make this clearer a very simple illustration would be:

Imagine you are investing \$10 today. You think that it will return \$12 in one year’s time. You like to make at least 10% return on your money when it is invested so your cost of capital is 10%.

The net present value of the \$12 today is \$10.9 (=£11/(1+10%)) so the \$12 in one year is worth more than the \$10 today, meaning that you should invest as the project will earn you more than the 10% return that you need.

Syntax of the NPV function in Excel is NPV( rate, value1, [value2], [value3], … )

rate is a required parameter and is the discount rate or cost of capital over a single period.

value1, value2,… are values of future income (positive value) and/or payments (negative value). Value 1 is a required parameter and subsequent values are optional. You will need to put a value, even if it is zero, in for each period of the project.

Below is a more complex example. We have a \$200,000 investment which returns different amounts each year for seven years.

We put the initial investment in cell B3. Initially we have the forecasted seven years of income in cells B4:B12. The cost of capital or annual discount rate is in cell B2.

After 7 periods our NPV is going to be:

If we add income for two additional periods, NPV is going to change:

Big news last evening from the Office 2010 Microsoft Office 2010 Engineering Blog: in the two weeks since the public release of the Office 2010 beta more than a million people have downloaded it. There is a lot of excitement out there about this new Office release. See for yourself by downloading your own copy from http://www.microsoft.com/2010/ Remember, however, that this is a beta release so follow the installation instructions carefully

Link Textbox Contents to Worksheet Data

Quote of the Day:

Copy from one, it’s plagiarism; copy from two, it’s research.

–Wilson Mizner

I tried a little experiment yesterday and was pleasantly surprised by the results. A while back I had learned how to link a chart title to data stored in an Excel worksheet. That in itself is an enhancement to charting.

If you have done any charting (graphing) in Excel, you will know that most of the chart’s values are stored in a worksheet. Change the worksheet values, and the chart will automatically update. Here’s an example. Let’s say that I am using a worksheet to prepare a monthly sales presentation to management. Management want to see the relative contribution of each salesperson to year to date sales. So I have set up a simple pie chart to display the information. Obviously, I have much more to do in my job than just to prepare this once a month presentation so I want to produce this chart as efficiently as possible.

Now, when I created the chart, using the chart wizard, I had to ‘hard code’ the title. That means that each month after I have updated the underlying date, I have to manually edit the chart title so that it reflect the reporting month. That takes a minute or two and for me it’s easy to forget. Forgetting to change the title costs more time. I have to re-open the file and make the change. Notice how the chart title at the moment is simply, “YTD Sales,” not reflecting the fiscal period.

To keep management of this chart as simple and efficient as possible I want to enter data only into the five cells that are highlighted in yellow. Of course the sales figure cells could actually be getting their values from formulas referring to the raw data. Then all I would have to do is enter the period ending date (and even that cell could contain a formula referring to the raw data.)

Here I have made one small change. The textbox containing the chart title no longer contains literal text. I have replaced the literal text with a formula that refers to a cell  containing the title exactly as I would like to see it in the chart. Now, regardless of the reporting date, the chart will always indicate the correct date.

These examples show an embedded chart. Having a date specific title is not as critical in embedded charts as it is in charts that are rendered on separate chart sheets in the workbook. In those cases, you can’t just glance at the data to see the reporting date  applicable to the chart without moving to the other worksheet.

The formulas revealed

The next graphic exposes the formulas involved. I have switched the worksheet to display formulas rather than the values generated by those formulas using ctrl+`. For this example, the sales results are simply random numbers because I don’t have any real data that can be published. The chart title text box contains a formula as well but it is still displaying the value rather than the actual formula which, in this case, is =\$D\$4.

Cell D4 itself contains a formula that creates and formats the text I would like as the chart title.

Now you ask, why would I want to take the time to set this up when I could simply edit the title every month? For me it’s a no brainer. Yes, it took some time to set up – probably less than five minutes, to be honest with you. What is the payback on that investment of my time. Sure it is small but let’s say it takes me 1-1/2 to 2 minutes each month to edit the title. In the first three months I have more than recovered my time investment.Then there is the intangible benefit of saving me the embarrassment of forgetting to change the title or occasionally misspelling something in the title. Besides, I just think this is a cool possibility.

Remember One Trick

The is just one small thing to remember when you want to try this yourself. Ordinarily, when you want to enter a formula you select a cell and then type the formula in either the cell or the formula bar.  When you want to enter a formula in a chart title text box, select the textbox but do not type anything in it; when you have the textbox selected, type the formula in the formula bar.