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//

Age	Outset Income	Cumulative Outset	Deferred Income	Cumulative Deffered
56	100	100	0	0
57	103	203	0	0
58	106.09	309.09	0	0
59	109.27	418.36	0	0
60	112.55	530.91	0	0
61	115.93	646.84	200	200
62	119.41	766.25	206	406
63	122.99	889.23	212.18	618.18
64	126.68	1015.91	218.55	836.73
65	130.48	1146.39	225.10	1061.83
66	134.39	1280.78	231.85	1293.68

<tbody>
</tbody>

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.

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

<tbody>
</tbody>