Hoping you folks can help me simplify ...
My application has a series of positive outlays over a number of years as well as a specified payoff amount for each year, also expressed as a positive number. (premium payments and death benefit, respectively) We can't know ahead of time which year the death benefit will be received, but for any arbitrary year we wish to calculate the internal rate of return of the payout on the scheduled outlays.
Assume we have data located as follows:
$A$11:$A$85 - Plan Year (1 - 75)
$B$11:$B$85 - Premium (outlays)
$C$11:$C$85 - Death Benefit (payout amount)
To calculate the IRR for plan year 10, I'd like to use:
=IRR((Offset($B$11:$B$85, 0, 0, $A20, 1), Index($C$11:$C$85, $A20)
This formula would work if the premiums were expressed as negative numbers (or, alternatively, if the death benefit were expressed as a negative number.) Since that is not the case, I am forced to set up an intermediate column to convert the outlays to negative values. Thus, I have
$D$11:$D$85 set to the formula = $A11 * -1 ; which means my IRR calc becomes
=IRR((Offset($D$11:$D$85, 0, 0, $A20, 1), Index($C$11:$C$85, $A20)
I can't figure out a way to effect the conversion to a range containing negative values in the same formula as the IRR calculation; in other words, without the need for an intermediate column. Any suggestions?
TIA!
E.B.
My application has a series of positive outlays over a number of years as well as a specified payoff amount for each year, also expressed as a positive number. (premium payments and death benefit, respectively) We can't know ahead of time which year the death benefit will be received, but for any arbitrary year we wish to calculate the internal rate of return of the payout on the scheduled outlays.
Assume we have data located as follows:
$A$11:$A$85 - Plan Year (1 - 75)
$B$11:$B$85 - Premium (outlays)
$C$11:$C$85 - Death Benefit (payout amount)
To calculate the IRR for plan year 10, I'd like to use:
=IRR((Offset($B$11:$B$85, 0, 0, $A20, 1), Index($C$11:$C$85, $A20)
This formula would work if the premiums were expressed as negative numbers (or, alternatively, if the death benefit were expressed as a negative number.) Since that is not the case, I am forced to set up an intermediate column to convert the outlays to negative values. Thus, I have
$D$11:$D$85 set to the formula = $A11 * -1 ; which means my IRR calc becomes
=IRR((Offset($D$11:$D$85, 0, 0, $A20, 1), Index($C$11:$C$85, $A20)
I can't figure out a way to effect the conversion to a range containing negative values in the same formula as the IRR calculation; in other words, without the need for an intermediate column. Any suggestions?
TIA!
E.B.
