I am adding this as I looked at the search results, and noticed that people had searched for IRR, and sadly they didn't find anything useful.

Here's a noddy's guide to IRR.

IRR is a function that takes a series of cash flows and works out the effective rate of return if they were to be annually invested.

Let's take a look.
Here's an example of an IRR function call:

I think IRR is easiest explained with a bond example
In this example, there are 4 cash flows
- at time 0 (today), I pay 100 for a bond (a negative number means you pay out money... positive means receive)
- at years 1 and 2, the bond pays me 10
- at maturity, it pays back the 100, and also 10.
... The technical name for this is: a 3 year 10% coupon bond trading at par, but don't worry too much abt that.

If you insert an IRR function call and input the cash flows, it returns 10%. This is the internal rate of return on the bond cash flows.

Now, let's look into this a bit deeper.
We'll extract the cash flows, invest them at each point, and grow them at 10% each year. If we sum them at the end, they sum to 0.

SO, another way to look at IRR is that it is the rate at which these cash flows invested sum to 0 at the end.

IRR Spreadsheet

As a side note, the last thing is that you will notice that the IRR function call has 2 arguments. The 2nd argument is a guess. The reason for this is that there's no mathematical way of solving this equation, so a numerical way is used. The only difference this makes for a sensible set of cash flows is in the speed of the calculation. The closer you are with your guess, the faster the calc. A normal user is unlikely to notice the difference.
To demonstrate this, in my example sheet, on the "Explained" tab.. select cell g5. Then go: Tools => Goal Seek
You want to set g5 = 0, by changing g2
That's: "Sum of invested cash flows" = 0, by changing "Investment Rate"

... the difference is small, and due to Excel giving up before it finds the right solution.. it thinks that 6 decimal places is good enough.

Hope that helps !

Nick

IRR training Video:

VLOOKUP is an extremely useful function and you should know it like the back of your hand.
In a few words, it allows you to look up a value in a table, and return another value on the same row.

In my example, you've bought yourself a massive Buy To Let portfolio, and are wondering when you can retire. On typing in the new prices, you realise this might not be any time soon.

Now, you'd like to be able to type in the purchase number, and return the new value.. so here's how to do it:

VLOOKUP example sheet

If you want to know for example the 5th property you bought, you type 5 in cell C3.

Here's a financial example use case for foreign currencies.
You have done a transaction in USD, and you want to convert the value into GBP.

VLOOKUP Currencies example sheet

Nick

Training Video on VLOOKUP:

If you need help, the last thing you want to do is to use Microsoft's help (pressing F1)... It's really rubbish.. Sorry MS !

The best place to look (IMHO) is google groups:

Google Groups

- this is where you're most likely to find an answer to your question as it's structured around a Problem => Solution framework, and someone else is sure to have encountered the same problem as you before... and solved it !

..or let Excel do it for you with the RANDBETWEEN function.

=RANDBETWEEN(1,10)

... useful for example if you're writing a card game, you can use =RANDBETWEEN(1,52) to pick a random card.

... or for a random walk analysis on a share price.

Training Video on Random Number Generation:

Did you know that you can get access to a whole load of new functions in Excel 2003 by adding the analysis toolpak ?

Go: Tools=>Addins and select Analysis toolpak

Now you can use functions like EDATE that allow you to add/subtract a specified amount of months to a start date.

Not a lot of people know this trick, but it can be a massive time saver if you have the same information on lots of sheets, and you want to change it.

It's simple when you know how !

Step1:
Select all the sheets that you want to enter a value into or change a value on. You can do this by holding down the CTRL key

Step2:
Simply type the value in the cell you want on the active sheet, and as if by magic, it appears on all the other sheets.

Nick

Training Video on how to Change Values on Multiple sheets:

This is a quirky one.

You can use the excel function call CELL("filename") to retrieve something that should be the file name, but for some odd reason isn't.

Fortunately, the function call does have a couple of uses -
You can use it to retrieve the file path by using a few text functions around it like so:

=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)
- returns file path (if you have saved the file).

Try it and see !

Training Video on getting the file path:

This tip is a very quick and easy one

Step1:
Type: "Monday" in cell A1

Step2:
Select cell A1

Step3:
Click on the square in the bottom right of the selection
- Drag it down until A7

You will now have all the days of the week.

Training Video on how to create days of the week in Excel: