Calculating monthly value of savings using an annual rate

[-Mike-]

Hi,

Hope you're all well. I'm new here and was wondering if I could get some help on an excel calculation I'm trying to do.

Essentially, I'm trying to work out how much each month an individual would get if they saved a fixed amount and received a fixed interest on their savings.

I'm using the future value to calculate this and have a fixed annual interest rate. Below is the formula i've used but the results I get come out incorrectly

=FV(1+interest rate)^(1/12)-1,(D6/12), -(mthly saving amount), -(initial amount),1)

I've attached my spreadsheet containing my formula if that would be more useful. Can anyone tell me what I'm doing wrong.


Thanks for any help

[NeedForExcel]

Put this in D8 and drag across -

=FV((1+$C$2)^(1/12)-1,(D6/12),-$C$8,-C8,1)

Or

=FV((1+$C$2)^(1/12)-1,(D6/12),-$C$8,-$C$8,1)

One of these should work.
Please let me know if it is OK.

[-Mike-]

Thanks for the quick reply Deep Dave. However, the results still look incorrect to me. For example, in the 12th month the result is 1004 which is a lot lower than what saving 500 a month + interest should be. Maybe i'm confused about what the FV function is supposed to do?

[JeteMc]

I believe that the formula

Please Login or Register  to view this content.

populated in C8 and dragged across will work. This assumes that you intend to make a deposit at the beginning of the month, based on your initial description of the formula.
Let me know if you have any questions.

[-Mike-]

Thank you so much JeteMc. That's exactly what I was looking for.

One question, if I wanted to allow for varying monthly savings amount, could i create another row of columns of differing mthly amounts and then change the $C3*$C4/12 part of the formula so that C3 changes to C4 etc.. when its dragged over? Would that be possible with the FV formula or would it lead to incorrect results?

Thanks again

[JeteMc]

You're welcome and thank you for the feedback. As to your question the Excel help on the FV function says, "Pmt Required. The payment made each period; it cannot change over the life of the annuity. ...", so I am guessing you would have to come up with a custom formula to do what you are asking.

[JeteMc]

Here is a formula that will do most of what you asked:

Please Login or Register  to view this content.

Populate C9 and across with this formula and you'll see that it yields the same results provided by the FV formula in row 8. If you put a value in one of the cells in row 10 the subsequent values in row 9 will increase/decrease correspondingly. The formula assumes as default a deposit of $500 if no value is put in row 10, so that if a deposit of less than $500 is going to be made for that month a negative value will have to be entered. Example, if in month 4 the deposit will be $400, a -100 will need to be placed in G10.

Let me know if there are any questions.