T
txheart
Hello all,
Good grief, I've had nothing but trouble with this spreadsheet! I'll be
SO glad when it's finally finished and we can go on to something
else...
This formula is using the same thought process as my question in an
earlier post
http://www.excelbanter.com/showthread.php?t=184072&highlight=txheart
This is an explanation of what my cells are doing
D20=His monthly cost (234.25), F20=Hers monthly cost (234.25)
x12= to get the yearly cost
x0.75 this amount is the yearly cost minus either 25% or 500, whichever
is lower)
D22=His current age in years (59), F22=Hers current age in years (59)
(79-D22)=years left in life using national average for him, hers is 83.
Typing it out that way I think I see where my problem is, but I'm only
more confused now instead of less.
I'm telling the formula that I need the yearly cost times 75% up to a
maximum of 500. I need the remaining 25% to be what is maxed at 500.
The yearly maximum a person can discount is 500 or 25%. To arrive at
their yearly cost after discount I'd need to somehow tell the
formula...
25% of the yearly cost would be 702.25. Since this amount is higher
than the allowed 500, we must use 500 instead. That is the amount of
the yearly discount allowed.
2811-500=2311 this is the amount I need to use now
2311*20 (the number of years left in the average lifespan taken from
D22 and F22 above in the explanations)=46220
His lifetime cost minus the yearly 25% or 500 is 46220
Now do hers using the same formula, except average lifespan is 83, not
79)
2311 (the total of the formula we came up with on the man's) * 24 (the
number of years left in her average lifespan of 83) = 55464
His total plus hers is 101684.00
The total they will pay up to age M79, F83 with their 25% yearly
discount or 500.
This is the amount that I need to come up with, does anyone have the
slightest idea how to get there?
Thank all of you for the help you've given me so far on this
spreadsheet!
Txheart
Good grief, I've had nothing but trouble with this spreadsheet! I'll be
SO glad when it's finally finished and we can go on to something
else...
This formula is using the same thought process as my question in an
earlier post
http://www.excelbanter.com/showthread.php?t=184072&highlight=txheart
This is an explanation of what my cells are doing
D20=His monthly cost (234.25), F20=Hers monthly cost (234.25)
x12= to get the yearly cost
x0.75 this amount is the yearly cost minus either 25% or 500, whichever
is lower)
D22=His current age in years (59), F22=Hers current age in years (59)
(79-D22)=years left in life using national average for him, hers is 83.
Typing it out that way I think I see where my problem is, but I'm only
more confused now instead of less.
I'm telling the formula that I need the yearly cost times 75% up to a
maximum of 500. I need the remaining 25% to be what is maxed at 500.
The yearly maximum a person can discount is 500 or 25%. To arrive at
their yearly cost after discount I'd need to somehow tell the
formula...
25% of the yearly cost would be 702.25. Since this amount is higher
than the allowed 500, we must use 500 instead. That is the amount of
the yearly discount allowed.
2811-500=2311 this is the amount I need to use now
2311*20 (the number of years left in the average lifespan taken from
D22 and F22 above in the explanations)=46220
His lifetime cost minus the yearly 25% or 500 is 46220
Now do hers using the same formula, except average lifespan is 83, not
79)
2311 (the total of the formula we came up with on the man's) * 24 (the
number of years left in her average lifespan of 83) = 55464
His total plus hers is 101684.00
The total they will pay up to age M79, F83 with their 25% yearly
discount or 500.
This is the amount that I need to come up with, does anyone have the
slightest idea how to get there?
Thank all of you for the help you've given me so far on this
spreadsheet!
Txheart