Setting up an excel spreadsheet for IRR

[Jason F]

I want to set up an excel spreadsheet to calculate my IRR on purchasing a note.

I have on the spreadsheet how to set up the loan terms to spit out a payment for each period (1-360). I'm looking for the forumla that will allow me to calculate my return on investment for each period assuming the note is paid off on at that time. Basically something that takes into account the monthly payment, as well as the equity received if the note is paid in full or refinanced. I want to be able to drag the formula down for each period.

Does anyone have an example formula or spreadsheet they could send me? I've tried the "IRR" function through excel but it is very confusing and I can't get it to work.

[Landon Scott]

I realize this answer is probably not needed at this point, but for furture reference, here goes: Excel provides an IRR calculation for a series of cash flows irrespective of the time interval. If you run IRR on monthly cash flows, you need to convert the monthly IRR: (1+monthly IRR)^12-1 = annual IRR. Likewise, if you want a monthly IRR from an annual rate you would reverse this: (1+annual IRR)^(1/12)-1. IRR is just the discount rate which gives a zero NPV, there is no forumula or ability to convert to different holding periods. Don't know if it helps, but Income Property Analytics site might help.