IRR: is there a quickest way?

[l]

Hi all!

If i have a series of payment that occur regularly in time (let's say
at the end of the year) but not in the amount, is there a way to
calculate IRR without writing down the entire plan?
I mean, a formula where i can say "this is the first amount for X
periods; this is the second amount for Y periods" and so on. In this
way, instead of writing a row/column for each period, we could have a
row/column for each series. This could be the case of mortgage payment
plan, with rate adjustable every year.
thanks in advance
l

 

[lorenzo]

any idea?

 

[MyVeryOwnSelf]

If i have a series of payment that occur regularly in time (let's say

at the end of the year) but not in the amount, is there a way to
calculate IRR without writing down the entire plan?
I mean, a formula where i can say "this is the first amount for X
periods; this is the second amount for Y periods" and so on. In this
way, instead of writing a row/column for each period, we could have a
row/column for each series. This could be the case of mortgage payment
plan, with rate adjustable every year.

One way is to start with the "row/column for each series" and automatically
generate the entire plan.

In the csv file below, column C has the size of a payment and column D has
the number of payments of the corresponding size. These are constants you
enter. Also put the ="" after the last item in column C.

The formulas in columns A and B are to get the entire plan into column A.
Then IRR can be applied to column A.

Adjust to suit your need.

---------------------- cut here ----------------------
10000,,,
"=-VLOOKUP(ROW()-2,B,2,TRUE)",0,1000,3
"=-VLOOKUP(ROW()-2,B,2,TRUE)",=B2+D2,1010,2
"=-VLOOKUP(ROW()-2,B,2,TRUE)",=B3+D3,900,6
"=-VLOOKUP(ROW()-2,B,2,TRUE)",=B4+D4,1100,5
"=-VLOOKUP(ROW()-2,B,2,TRUE)",=B5+D5,"=""""",
"=-VLOOKUP(ROW()-2,B,2,TRUE)",,,
"=-VLOOKUP(ROW()-2,B,2,TRUE)",,,
"=-VLOOKUP(ROW()-2,B,2,TRUE)",,,
"=-VLOOKUP(ROW()-2,B,2,TRUE)",,,
"=-VLOOKUP(ROW()-2,B,2,TRUE)",,,
"=-VLOOKUP(ROW()-2,B,2,TRUE)",,,
"=-VLOOKUP(ROW()-2,B,2,TRUE)",,,
"=-VLOOKUP(ROW()-2,B,2,TRUE)",,,
"=-VLOOKUP(ROW()-2,B,2,TRUE)",,,
"=-VLOOKUP(ROW()-2,B,2,TRUE)",,,
"=-VLOOKUP(ROW()-2,B,2,TRUE)",,,,




=IRR(A1:A17),,,,