FV Function result is unexpected.

[Guest]

Could anyone please help me to rectify the following problem.

Compounded Half-yearly ie 2
ROI 8% (=8%/2) ie 0.04
Term(Yrs) 6
Term (Months) 0
TotTerm 6 (=6*2) ie 12
Instalment 1000 (=1000/2) ie 500


If I use Excel FV Function like this
FV(0.04, 12, -500, 0, 1)

The result becomes 7813.42

But if I calculate in the following way:

1000 6 =ROUND(1000*(1+(0.04))^(12),0) = 1601
1000 5 =ROUND(1000*(1+(0.04))^(10),0) = 1480
1000 4 =ROUND(1000*(1+(0.04))^(8),0) = 1369
1000 3 =ROUND(1000*(1+(0.04))^(6),0) = 1265
1000 2 =ROUND(1000*(1+(0.04))^(4),0) = 1170
1000 1 =ROUND(1000*(1+(0.04))^(2),0) = 1082

The result becomes 7967

The difference is being 153.58

I would like to know the correct Excel Function.

Regards,

 

[JE McGimpsey]

You're using the correct formula. Your error in your second method is to
calculate your interest as 1000 for an even number of periods, instead
of 500 for each period. The correct calculation is

500 12 =ROUND(500*(1+0.04)^12) = 801
500 11 =ROUND(500*(1+0.04)^11) = 770
500 10 =ROUND(500*(1+0.04)^10) = 740
500 9 =ROUND(500*(1+0.04)^9) = 712
500 8 =ROUND(500*(1+0.04)^8) = 684
500 7 =ROUND(500*(1+0.04)^7) = 658
500 6 =ROUND(500*(1+0.04)^6) = 633
500 5 =ROUND(500*(1+0.04)^5) = 608
500 4 =ROUND(500*(1+0.04)^4) = 585
500 3 =ROUND(500*(1+0.04)^3) = 562
500 2 =ROUND(500*(1+0.04)^2) = 541
500 1 =ROUND(500*(1+0.04)^1) = 520

For a total of 7814, the difference with the FV function being due to
your rounding.

 

[Guest]

Actually investment is made anually. ie Rs. 1000 at the begining of every
year for 6 years.

As per the rule for Rs. 1000 maturity value is Rs. 1601. ROI is 8%
compounded half-yearly.
The calculation is like follows:
1000 1 1082 82
1082 1 1170 88
1170 1 1265 95
1265 1 1368 103
1368 1 1480 112
1480 1 1601 121
Total: 601

In my opinion if I calculate by taking the deposit half ie on the basis of
Rs. 500 result may be wrong.

Your opinion is expected.

 

[joeu2004]

Could anyone please help me to rectify the following problem.
Compounded Half-yearly ie 2
ROI 8% (=8%/2) ie 0.04
Term(Yrs) 6
Term (Months) 0
TotTerm 6 (=6*2) ie 12
Instalment 1000 (=1000/2) ie 500

Actually investment is made anually. ie Rs. 1000 at the begining of every
year for 6 years

The correct answer is 7966.62, if you do not round any values. This
can be computed as follows:

=fv(fv(8%/2, 2, 0, -1)-1, 6, -1000, 0, 1)

According to your later posting, you invest 1000 once, but the
compounding frequency is semi-annually. Therefore, the annual payment
is compounded at the rate FV(8%/2,2,0,-1)-1 -- the semi-annual nominal
rate compounded.

You can confirm this by setting up the following model:

A1:A13: period number (0-12)
B1:B13: periodic investment (1000) in B2, B4,..., B12
C1:C13: periodic ending balance
C1: 0
C2: =(C1+B2)*(1+8%/4)
Copy C2 into C3:C13

When you say "ROI", I am interpreting you to mean nominal annual
rate. That is consistent with everything else you wrote.

But if I calculate in the following way:
1000 6 =ROUND(1000*(1+(0.04))^(12),0) = 1601
[... etc ...]
1000 1 =ROUND(1000*(1+(0.04))^(2),0) = 1082
The result becomes 7967

And that seems to agree with my computation. But beware: rounding
intermediate results can lead to a big difference over many (more)
periods. I am not saying that is wrong to do. It depends on what
happens in reality.

 

[JE McGimpsey]

OK - I assumed that you gave semi-annual installments based on your
example.

Since your problem is an annual payment compounded semiannually, you can
solve it by calculating the effective rate:

=(1 + 8%/2)^2 = 1.0816

so your FV calculation is

=FV((1 + 8%.2)^2, 6, -1000, 0, 1)

=7966.62

 

[Guest]

Thanks to you and Mr. joeu2004

Please check the following:

ROI : 8% Compounded Half-yearly ie 0.0816.


Table TT PPT Investment NSC Return NSC Return
0 1 2 Difference

Table 014 31 31 14268 1,962,703 0 1,962,703
=ROUND(FV(0.0816,31,-14268,0,1),0)
=ROUND(FV(0.0816,0,-1962703,0,1),0)

Table 048 25 20 20133 1,014,344 6,456,899 7,471,243 1,629,635 5,841,608

=ROUND(FV(0.0816,20,-20133,0,1),0)
=ROUND(FV(0.0816,5,-1014344,0,1),0)


Now look at Table 048. If I calculate in two parts the result is being
different.

The schemes matures in every six years and I need to reinvestment manually.

If break my calculation with six years intervals I thank the amount will be
different.

Then How I will proceed for 31 years for Table 014 and 25 years for Table 048

In my example TT means Total Term and PPT means Premium/Instalment Paying
Term.

Thanks in advance.

 

[joeu2004]

Please check the following:
ROI : 8% Compounded Half-yearly ie 0.0816.

I would not use the rounded APY of 8.16%. Instead, I would compute
the compounded rate using one of the methods mentioned in earlier
postings. Small differences in interest rates can make noticable
differences in the long run.

Table 014 [...]
[....]
=ROUND(FV(0.0816,31,-14268,0,1),0)
=ROUND(FV(0.0816,0,-1962703,0,1),0)

Table 048 [...]
[....]
=ROUND(FV(0.0816,20,-20133,0,1),0)
=ROUND(FV(0.0816,5,-1014344,0,1),0)

The second formula in each cases, especially Table 048, should be of
the form:

=round(fv(0.0816, 5, 0, -1014344, 1), 0)

Note that 1014344 (the result of the 20-year PPT) is the "present
value" (principal), not a periodic payment, for the remainder of the
TT.

Now look at Table 048. If I calculate in two parts the result is being different.
The schemes matures in every six years and I need to reinvestment manually.
If break my calculation with six years intervals I thank the amount will be
different.
Then How I will proceed for 31 years for Table 014 and 25 years for Table 048

One generalized approach (using Table 048 as an example):

A1: annual nominal rate (8%)
A2: annual rate compounded semi-annually: =fv(8%/2, 2, 0, -1) -1
A3: period payment during PPT (20133)
A4: total term (TT), in years (25)
A5: total premium payment term (PPT), in years (20)
A6: reinvestment frequency for each PPT, in years (6)
A7: initial year number (0)
B7: initial investment (0)
A8: reinvestment year number (see below)
B8: ending balance for each reinvestment period (see below)
Copy A8:B8 down to A9:B9 etc until the next column A would be blank

The formulas in A8:B8 depend on what you must do for the remaining TT
after the last PPT.


Model 1: Separate reinvestment for the remaining TT

A8: =if( A7 = $A$4, "", if(A7 < $A$5, min(A7+$A$6, $A$5), $A$4) )

B8: =if( A8 = "", "", round(fv($A$2, A8-A7, -$A$3*(A8<=$A$5), -B7,
1), 0) )

This puts the results for the last PPT and for the remaining TT on a
separate lines, even if the last PPT is not a multiple of 6 (A6). The
"pmt" argument for the remaining TT will be zero because year number
is more than the PPT (A8 > $A$5).


Model 2: Extend last PPT to include remaining TT

A8: =if( A7 = $A$4, "", if(A7+$A$6 < $A$5, A7+$A$6, $A$4) )

B8: =if( A8="", "",
round(fv($A$2, ($A$4-$A$5)*(A8>$A$5), 0, -round(fv($A$2,
min(A8-A7, $A$6)*(A7<$A$5), -$A$3, -B7, 1), 0)), 0) )

This includes the result for the last PPT with the result for the
remaining TT, if the PPT is not a multiple of 6 (A6). The "nper"
argument for the first FV() is zero except for the last entry, which
includes the remaining TT. The "nper" argument for the second FV() is
zero when the last entry does not include the last PPT. When "nper"
is zero, the result of FV() is simply the "pv" argument.

Caveat: I have not tested these formulas. "That is left as an
exercise for the student".

 

[JE McGimpsey]

I would not use the rounded APY of 8.16%.

Why not? (it's not rounded, by the way)

You use a lot of additional overhead:

fv(8%/2, 2, 0, -1) -1

to calculate the compounded rate.

But XL uses this calculation to calculate fv:

fv = -pv * (1 + rate)^nper - pmt * (1 + rate * type) *
((1 + rate)^nper - 1)/rate

With a pmt of 0, that reduces to

fv = -pv * (1 + rate) ^ nper

or, in the OP's example,

fv(8%/2,2,0,-1)-1 = -(-1 * (1 + 8%/2)^2) - 1

= 8.16%

As long as the Pmt term is zero, you can ALWAYS calculate the compounded
rate as

(1 + rate/nper)^nper - 1.

Small differences in interest rates can make noticable
differences in the long run.

Not in this case.

 

[joeu2004]

Why not? (it's not rounded, by the way)

In this particular, you are correct. Mine was a general comment. It
is extremely rare that the APY is exact when derived from the nominal
interest rate.

You use a lot of additional overhead:
fv(8%/2, 2, 0, -1) -1

To each his own. I tend to offer approaches that use Excel formulas
because they are easy to get Help on. However, I usually do offer
both when an arithmetic expression is transparent. Since you did that
already, I did not see the need to repeat it. You might notice that I
did refer to "using one of the methods mentioned in earlier
postings".