Future value of monthly saving

[shardon]

Hello
I am trying to use Excel FV function to calculate future value of a fixed monthly payment of $800.00 with 6% interest over 7 months period. The deposit is made at the beginning of the period. I'll come up with wrong sum. One thing that confuses me is the term. Since it is 7 month, I don't know how to incorporate it into the formula. What am I doing wrong? I appreciate your help.
Feb 1st 800.00
March 1st 800.00
April 1st 800.00
May 1st 800.00
June 1st 800.00
June 1st 800.00
July 1st 800.00
Future value on July 31st?

Shardon

[FlameRetired]

What formula are you using? It's hard to tell without that.

Oh and what is the "wrong sum" you are coming up with?

[alansidman]

v	A	B
1	Date	Amount
2	2/1/2020	-800
3	3/1/2020	-800
4	4/1/2020	-800
5	5/1/2020	-800
6	6/1/2020	-800
7	7/1/2020	-800
8		$4,884.70

Formula in B8 is =FV(0.06/12,6,-800,,1)

[shardon]

FV(rate,nper,pmt,pv,type)
$6541.55

[FlameRetired]

@shardon

Please share the formula and figures you used to get that.

[joeu2004]

FV(rate,nper,pmt,pv,type)
$6541.55

.... Which is useless without showing us the values of the parameters.

Frankly, I cannot figure out what you did wrong. In your original posting, you duplicated the Jun 1 payment and counted 7 deposits instead of 6.

In any case, alansidman showed you the correct formula an hour earlier, although I would write it =FV(6%/12, 6, -800, 0, 1). Personal preference.

It might also be prudent to point out the difference between reality and Excel financial functions.

Excel financial functions assume that payments are made at regular intervals; "monthly", in this case.

In reality, interest is calculated based on the average daily balance for the exact number of days, which might vary between 28 and 31.

Other differences: (1) monthly interest is usually rounded; and (2) the daily interest rate is usually 1/366 or 1/365 of the annual rate, depending on whether or not the year of the month is a leap year.

So Excel financial functions can only be used to estimate results, not calculate them exactly.

The following table demonstrates the difference.

	A	B	C	D	E	F	G
1	date (MDY)	days	beg bal	deposit	end int	FV	FV error
2	2/01/2020	29	0.00	800.00	3.80
3	3/01/2020	31	803.80	800.00	8.15	804.00	0.20
4	4/01/2020	30	1,611.95	800.00	11.86	1,612.02	0.07
5	5/01/2020	31	2,423.81	800.00	16.38	2,424.08	0.27
6	6/01/2020	30	3,240.19	800.00	19.87	3,240.20	0.01
7	7/01/2020	31	4,060.06	800.00	24.70	4,060.40	0.34
8	8/01/2020		4,884.76			4,884.70	-0.06

Please Login or Register  to view this content.

Note: For simplicity, the test for leap year works only for non-century years.

[shardon]

From Excel Formula, insert function financial, FV
FV(0.005,7,800,800,1)=($6,541.55)

[shardon]

I am grateful to Flameretired, alansidman and joeu2004 for taking time and responding to my problem. Using all the info I received, I understood how to use the formula to calculate the future value. Thanks again

Shardon

[FlameRetired]

....
Note: For simplicity, the test for leap year works only for non-century years.

FWIW: For leap years including end of century years

Please Login or Register  to view this content.

if it helps.

[FlameRetired]

Good deal! Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.