I come up with $7395.89, total should be $7449.23
Who says? How do you know what the total should be?
PV=1070.52 (cell c8)
Period 1 9% (cell C16) for 8 years (cell D17) 1 month (cell E16)
Period 2 8% (cell C17) for 5 years (cell D17)
Period 3 7% (cell C18) for 12 years (cell D18) and 7 months (cell E18)
[...]
I believe my problem is how the months are bieng calculated
Yes. More to the point, you neglect to say what the compounding
frequency is; and if it is annual, how fractional years should be
handled.
First, there is a much easier way to do the computation. I prefer to
compute the FV of each period. But in a single formula, you could
write:
=FV(C18, D18+E18/12, 0, -FV(C17, D17+E17/12, 0, -FV(C16, D16+E16/12,
0, -C8)))
But that merely duplicates you assumptions, which are not
unreasonable.
I suspect the difference is because your assumptions are incorrect. I
tried different assumptions, but the results do not match what you say
is the expected result.
Because the expected result is higher, we might assume that the
interest rates (D) are nominal rates, and the compounding frequency is
monthly. Thus:
=FV(C18/12, 12*D18+E18, 0, -FV(C17/12, 12*D17+E17, 0, -FV(C16,
12*D16+E16, 0, -C8)))
But in that case, the result is 7,923.85 -- too high by a long shot.
Alternatively, we might assume that the compounding frequency is
annual and we should prorate fractional periods. Thus:
=FV(C18, D18, 0, -FV(C17, D17, 0, -FV(C16, D16, 0, -C8)*(1 +
E16*C16/12)))*(1 + E18*C18/12)
Perhaps now you can see why I prefer to break the computation down by
period. For simplicity, I eliminated the proration of Period 2, since
we know E17 is zero.
But that result is 7402.14 -- a little too low.
At this point, I lose my patience with trying to second-guess what is
additional information that either you should have or you should ask
for.
Good luck!
PS: Since the result with monthly proration is off by only about
0.63%, perhaps we should use daily proration. Note that 0.63% of 365
is only 2.3 days. But that would require knowing the actual starrt
and end dates for each period.
----- original posting -----
