IRR: is there a quickest way?
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 |
IRR: is there a quickest way?
any idea?
l wrote: 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 |
IRR: is there a quickest way?
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:D,2,TRUE)",0,1000,3 "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",=B2+D2,1010,2 "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",=B3+D3,900,6 "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",=B4+D4,1100,5 "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",=B5+D5,"=""""", "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",,, "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",,, "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",,, "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",,, "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",,, "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",,, "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",,, "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",,, "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",,, "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",,, "=-VLOOKUP(ROW()-2,B:D,2,TRUE)",,,, =IRR(A1:A17),,,, |
All times are GMT +1. The time now is 12:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com