-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
I think you mean 5% annually, paid monthly.
One question is whether the annual rate is a simple interest rate or a compounded rate ("yield")?
One of the following formulas should work for you:
=FV(5%/12, 5*12, 0, -1000000)
or
=FV((1+5%)^(1/12)-1, 5*12, 0, -1000000)
The first formula treats 5% as simple interest. The second formula treats 5% as a compounded yield.
I use zero for payments because you do not mention any.
If you do have periodic (monthly?) "payments", you need to tell us whether they are payments in (contributions) or out (withdrawals), as well as the amount. But in order to use the FV function, all payments must be equal, and they must occur regularly (e.g. every month).
Finally, if you do have periodic payments, you need to tell us whether they occur at the beginning or end of the period. Think of it this way: is interest earned on the first payment? And is interest earned on the last payment?
The key to using (most) Excel financial functions is signed cash flows. You need to use opposite signs for "in" and "out" cash flows. It does not matter which sign that you use for "in"; but you must the opposite sign for "out".
I usually choose signs so that the function result (FV) is positive. Since FV is "out", I use negative for "in" (initial investment and any contributions).
One question is whether the annual rate is a simple interest rate or a compounded rate ("yield")?
One of the following formulas should work for you:
=FV(5%/12, 5*12, 0, -1000000)
or
=FV((1+5%)^(1/12)-1, 5*12, 0, -1000000)
The first formula treats 5% as simple interest. The second formula treats 5% as a compounded yield.
I use zero for payments because you do not mention any.
If you do have periodic (monthly?) "payments", you need to tell us whether they are payments in (contributions) or out (withdrawals), as well as the amount. But in order to use the FV function, all payments must be equal, and they must occur regularly (e.g. every month).
Finally, if you do have periodic payments, you need to tell us whether they occur at the beginning or end of the period. Think of it this way: is interest earned on the first payment? And is interest earned on the last payment?
The key to using (most) Excel financial functions is signed cash flows. You need to use opposite signs for "in" and "out" cash flows. It does not matter which sign that you use for "in"; but you must the opposite sign for "out".
I usually choose signs so that the function result (FV) is positive. Since FV is "out", I use negative for "in" (initial investment and any contributions).
Last edited:
Upvote
0
If you withdraw just the interest every month, the principal is never decreased, in theory. So FV is the same as the PV, namely $1M.
FYI, the monthly interest amount is probably =1000000*5%/12, which is $4166.67. Due to rounding up, the actual FV is $999,999.77, to wit:
=FV(5%/12, 5*12, 4166.67, -1000000)
Withdrawals are at the end of the period; so type=0 (implied 5th parameter).
But if you are talking about an investment with an annual yield of 5%, the monthly return is =1000000*((1+5%)^(1/12)-1), which is $4074.12. Due to rounding down, the actual FV is $1,000,000.26, to wit:
=FV((1+5%)^(1/12)-1, 5*12, 4074.12, -1000000)
In both case, note that the sign of the withdrawal ("payment") is the opposite of the investment and the same as the FV.
I might note that all of these calculations are estimates, because in real life, we do not know how the financial institution rounds interest payments and the cumulative balance.
The Excel FV function does not round internally, since it cannot know the intended rounding rules to apply.
FYI, the monthly interest amount is probably =1000000*5%/12, which is $4166.67. Due to rounding up, the actual FV is $999,999.77, to wit:
=FV(5%/12, 5*12, 4166.67, -1000000)
Withdrawals are at the end of the period; so type=0 (implied 5th parameter).
But if you are talking about an investment with an annual yield of 5%, the monthly return is =1000000*((1+5%)^(1/12)-1), which is $4074.12. Due to rounding down, the actual FV is $1,000,000.26, to wit:
=FV((1+5%)^(1/12)-1, 5*12, 4074.12, -1000000)
In both case, note that the sign of the withdrawal ("payment") is the opposite of the investment and the same as the FV.
I might note that all of these calculations are estimates, because in real life, we do not know how the financial institution rounds interest payments and the cumulative balance.
The Excel FV function does not round internally, since it cannot know the intended rounding rules to apply.
Last edited:
Upvote
0
PS.... I assumed that interest is paid to the account at the same frequency as the withdrawals, namely monthly. That is, you are withdrawing monthly interest after it is paid.
Are you, instead, making monthly withdrawals, but interest is paid annually?
Please say "no". That would complicate the calculation significantly.
Are you, instead, making monthly withdrawals, but interest is paid annually?
Please say "no". That would complicate the calculation significantly.
Upvote
0
