Retirement Deferral Comparison

[chrislowe2006]

Hello all,

Thanks in advanced for your assistance!

I am trying to create a formula for calculating defferal periods for annuities, based on a comparison taking an income straight away or deffering it X amount of years down the line to show if it is worth doing.

The initial and deferred income can be subject to a fixed escalation rate in % ranging from 0-10%. For the example below they are both done on 3%.

So the user will enter 6 Variables: //Variables used in example below//

- Age //56//
- Outset income //£100//
- Outset Escalation //3%//
- Deferred Income //£120//
- Deferred Escalation //3%//
- Deferred Period //5yrs//


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Age
[/TD]
[TD]Outset Income
[/TD]
[TD]Cumulative Outset
[/TD]
[TD]Deferred Income
[/TD]
[TD]Cumulative Deffered
[/TD]
[/TR]
[TR]
[TD]56
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]57
[/TD]
[TD]103
[/TD]
[TD]203
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]58
[/TD]
[TD]106.09
[/TD]
[TD]309.09
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]59
[/TD]
[TD]109.27
[/TD]
[TD]418.36
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]60
[/TD]
[TD]112.55
[/TD]
[TD]530.91
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]61
[/TD]
[TD]115.93
[/TD]
[TD]646.84
[/TD]
[TD]200
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]62
[/TD]
[TD]119.41
[/TD]
[TD]766.25
[/TD]
[TD]206
[/TD]
[TD]406
[/TD]
[/TR]
[TR]
[TD]63
[/TD]
[TD]122.99
[/TD]
[TD]889.23
[/TD]
[TD]212.18
[/TD]
[TD]618.18
[/TD]
[/TR]
[TR]
[TD]64
[/TD]
[TD]126.68
[/TD]
[TD]1015.91
[/TD]
[TD]218.55
[/TD]
[TD]836.73
[/TD]
[/TR]
[TR]
[TD]65
[/TD]
[TD]130.48
[/TD]
[TD]1146.39
[/TD]
[TD]225.10
[/TD]
[TD]1061.83
[/TD]
[/TR]
[TR]
[TD]66
[/TD]
[TD]134.39
[/TD]
[TD]1280.78
[/TD]
[TD]231.85
[/TD]
[TD]1293.68
[/TD]
[/TR]
</tbody>[/TABLE]

My current issue is getting a formula that will leave the correct number of rows blank in the red section that relate to the number of years deffered, so the example shows 5 years.

Then after the relevant number of years the payments begin and it calculates the income using the starting income specified.

I bet it is really simple but I can't figure it out .

Thanks again,

Chris.

 

[BGY23]

Hi,
If all you want is for the Deferred years to be Blank / zero then......
Lets say your Variables are in the range A1:B6 and your table is in the range A10: E21.

In D11 type =if($A11<$B$1+$B$6,0,your formula to calculate Deferred income) and copy it down.
In E11 type =if($A11<$B$1+$B$6,0,your formula to calculate Cumualtive Deferred) and copy it down.

Good luck

 

[BGY23]

Hi
If you want the cells to be blank the replace the ,0, with ,"",

 

[chrislowe2006]

Hi BGY23,

That works great, I knew it wouldn't be too difficult I just couldn't think what I had to do. I'm pretty new to actually creating more complex formulas so it's helped alot.

My final formula incase anyone else needs to reference this in the future is below, this was used in column F:

=IF($C11<$A$16+$A$31,0,IF(F10=0,A$25,SUM(F10*A$28,F10)))

C11= Clients Age located in table.
A16 = Clients Age as Variable.
A31 = Deferred Period in years
F10 = Deferred Income.
A25 = Initial Deferred Income as variable.
A28 = Deferred Escalation Rate.

The cumulative values go off of whats in column F so the previous formula worked fine.

[TABLE="class: cms_table_grid, width: 500, align: center"]

<tbody>[TR]
[TD]C
Age[/TD]
[TD]D
Outset Income[/TD]
[TD]E
Cumulative Outset[/TD]
[TD]F
Deferred Income[/TD]
[TD]G
Cumulative Deffered[/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD]103[/TD]
[TD]203[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]58[/TD]
[TD]106.09[/TD]
[TD]309.09[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]59[/TD]
[TD]109.27[/TD]
[TD]418.36[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]112.55[/TD]
[TD]530.91[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]115.93[/TD]
[TD]646.84[/TD]
[TD]200[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]62[/TD]
[TD]119.41[/TD]
[TD]766.25[/TD]
[TD]206[/TD]
[TD]406[/TD]
[/TR]
[TR]
[TD]63[/TD]
[TD]122.99[/TD]
[TD]889.23[/TD]
[TD]212.18[/TD]
[TD]618.18[/TD]
[/TR]
[TR]
[TD]64[/TD]
[TD]126.68[/TD]
[TD]1015.91[/TD]
[TD]218.55[/TD]
[TD]836.73[/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD]130.48[/TD]
[TD]1146.39[/TD]
[TD]225.10[/TD]
[TD]1061.83[/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]134.39[/TD]
[TD]1280.78[/TD]
[TD]231.85[/TD]
[TD]1293.68[/TD]
[/TR]
</tbody>
[/TABLE]