Figuring interest at a per-month rate, compounded monthly
June 9, 2010 8:25 AM Subscribe
Mathfilter: How do I figure the total value of interest at a per-month rate, compounded monthly, with monthly contributions?
Let’s say I start five years ago with $0 and put $100 per month into a savings account that yields interest of 2% per month compounded monthly. I do this for two years and then stop contributing; I then just let it sit and accrue more interest at the same rate (i.e., 2% per month compounded monthly).
How do I figure out how much money I should have today?
I’m not awful at math, but I haven’t done calculus in 10 years and the online calculators are all saying different things so I don’t quite trust them. If it was annual interest compounded annually I could do it, but the monthly interest and monthly compounding are throwing me.
These are hypothetical numbers analogous to the real-life figuring I’m trying to do, so the formula and the calculations are what I’m after, not so much the number.
Let’s say I start five years ago with $0 and put $100 per month into a savings account that yields interest of 2% per month compounded monthly. I do this for two years and then stop contributing; I then just let it sit and accrue more interest at the same rate (i.e., 2% per month compounded monthly).
How do I figure out how much money I should have today?
I’m not awful at math, but I haven’t done calculus in 10 years and the online calculators are all saying different things so I don’t quite trust them. If it was annual interest compounded annually I could do it, but the monthly interest and monthly compounding are throwing me.
These are hypothetical numbers analogous to the real-life figuring I’m trying to do, so the formula and the calculations are what I’m after, not so much the number.
You need to calculate this using an annuity formula with a 2% yield, 24 periods in total, and $100 instalment payments. That should generate the amount of money you'll have in your bank account at the end of two years.
Wrap that around a basic compound interest formula for another 36 periods at 2% interest. I have a rough estimate of $6,329.
posted by Phire at 8:40 AM on June 9, 2010 [1 favorite]
Wrap that around a basic compound interest formula for another 36 periods at 2% interest. I have a rough estimate of $6,329.
posted by Phire at 8:40 AM on June 9, 2010 [1 favorite]
You need algebra, not calculus, for this problem.
The annuity formula to which Phire refers is correct.
posted by dfriedman at 8:49 AM on June 9, 2010
The annuity formula to which Phire refers is correct.
posted by dfriedman at 8:49 AM on June 9, 2010
You need algebra, not calculus, for this problem
Technically you do need calculus but the formulas everyone throws around are just the end result of that so it becomes algebra
use .xls or google docs for this fv(2%,24,-100,,1)*1.02^36
you need the 1 because you are making the payment at the beginning of the time period,
posted by JPD at 9:00 AM on June 9, 2010
Technically you do need calculus but the formulas everyone throws around are just the end result of that so it becomes algebra
use .xls or google docs for this fv(2%,24,-100,,1)*1.02^36
you need the 1 because you are making the payment at the beginning of the time period,
posted by JPD at 9:00 AM on June 9, 2010
This is easy to do in Excel (or Google docs, or OpenOffice). The formula you need is future value (FV).
FV(rate,nper,pmt,pv,type)
Rate is the interest rate (make sure you include the percent sign).
Nper is the number of payments.
Pmt is the amount of the payment (amounts that you pay are negative, amounts withdrawn are positive.
PV is the present value. This is a single lump sum that is compounded over time.
Type (0 or 1) indicates whether the payment is due at the beginning or end of the period. Generally it is due at the beginning so should be set to 1.
So for the first two years you have:
FV (2%, 24, -100, 0, 1)
For the next three years you have:
FV (2%, 36, 0, X, 1)
where X is the lump sum accumulated in the first step. So you just embed the first calculation in the second calculation. Note you need a negative sign before the second FV because that is a contribution.
FV (2%, 36,0, -(FV (2%, 24, -100, 0, 1)), 1)
If you plug that into your spreadsheet you will get $6329.83.
posted by JackFlash at 9:27 AM on June 9, 2010 [1 favorite]
FV(rate,nper,pmt,pv,type)
Rate is the interest rate (make sure you include the percent sign).
Nper is the number of payments.
Pmt is the amount of the payment (amounts that you pay are negative, amounts withdrawn are positive.
PV is the present value. This is a single lump sum that is compounded over time.
Type (0 or 1) indicates whether the payment is due at the beginning or end of the period. Generally it is due at the beginning so should be set to 1.
So for the first two years you have:
FV (2%, 24, -100, 0, 1)
For the next three years you have:
FV (2%, 36, 0, X, 1)
where X is the lump sum accumulated in the first step. So you just embed the first calculation in the second calculation. Note you need a negative sign before the second FV because that is a contribution.
FV (2%, 36,0, -(FV (2%, 24, -100, 0, 1)), 1)
If you plug that into your spreadsheet you will get $6329.83.
posted by JackFlash at 9:27 AM on June 9, 2010 [1 favorite]
Response by poster: To those who are pointing out the online calculators, thank you, but as I said in my post I am looking for interest at a monthly rate (this is not the same as an annual rate), compounded monthly, and none of the calculators will do this. I promise I did google this before posting!
I am familiar with present/future value but again, that is not quite what I'm looking for.
So far the best I've come up with is trying to rough out what the monthly interest rate works out to annually, and using this calculator for the contribution period and then figuring interest for the remainder using the periodic compounding formula here.
For the above, I've been estimating 2% per month is equivalent roughly to 24% per year (2% per month x 12 months = 24%), and I have seen similar calculations in this particular situation done using approximately that figure, but does anyone have any idea whether that is correct?
Again, thanks for the responses...
posted by AV at 10:31 AM on June 9, 2010
I am familiar with present/future value but again, that is not quite what I'm looking for.
So far the best I've come up with is trying to rough out what the monthly interest rate works out to annually, and using this calculator for the contribution period and then figuring interest for the remainder using the periodic compounding formula here.
For the above, I've been estimating 2% per month is equivalent roughly to 24% per year (2% per month x 12 months = 24%), and I have seen similar calculations in this particular situation done using approximately that figure, but does anyone have any idea whether that is correct?
Again, thanks for the responses...
posted by AV at 10:31 AM on June 9, 2010
Best answer: I am very confused. How is this not a FV problem?
2% monthly = 1.02^12 -1 annually = 26.82%
posted by JPD at 10:40 AM on June 9, 2010
2% monthly = 1.02^12 -1 annually = 26.82%
posted by JPD at 10:40 AM on June 9, 2010
To those who are pointing out the online calculators, thank you, but as I said in my post I am looking for interest at a monthly rate (this is not the same as an annual rate), compounded monthly, and none of the calculators will do this. I promise I did google this before posting!
So use one of those calculators and treat months as if they were years.
posted by ROU_Xenophobe at 10:53 AM on June 9, 2010
So use one of those calculators and treat months as if they were years.
posted by ROU_Xenophobe at 10:53 AM on June 9, 2010
Using donmateo's link, just set initial amount to 1, monthly deposit to 8.33, annual interest compounded annually to 2, and number of years to 60.
posted by ROU_Xenophobe at 10:56 AM on June 9, 2010
posted by ROU_Xenophobe at 10:56 AM on June 9, 2010
Future value is exactly what you're looking for. Financial terms don't always mean what common sense might lead you to believe they would.
Phire, JPD* and JackFlash have it right. Note that the FV function expects a periodic interest rate, so it's proper to feed the function 2% as its interest rate parameter.
Roughing it out won't get you the right answer. 2% per month compounded monthly would likely be quoted as a 24% interest rate compounded monthly, but this yields ((1.02)^12) - 1 = 26.824% annually.
--------------------------------
*Actually, you don't need calculus -- this is a discrete math problem and iterative calculation will get you to the correct answer -- but it's all good. Doing a monthly cashflow model up in a spreadsheet will get you to the same place as the FV function call, and it wouldn't surprise me if that's exactly how the FV function works.
posted by Opposite George at 11:00 AM on June 9, 2010
Phire, JPD* and JackFlash have it right. Note that the FV function expects a periodic interest rate, so it's proper to feed the function 2% as its interest rate parameter.
Roughing it out won't get you the right answer. 2% per month compounded monthly would likely be quoted as a 24% interest rate compounded monthly, but this yields ((1.02)^12) - 1 = 26.824% annually.
--------------------------------
*Actually, you don't need calculus -- this is a discrete math problem and iterative calculation will get you to the correct answer -- but it's all good. Doing a monthly cashflow model up in a spreadsheet will get you to the same place as the FV function call, and it wouldn't surprise me if that's exactly how the FV function works.
posted by Opposite George at 11:00 AM on June 9, 2010
It is how the function works. And yes I'm aware that you can approximate the answer using a cashflow model - but it is still like the first thing you learn in high school calc when you get to the chapter on integration.
posted by JPD at 11:12 AM on June 9, 2010
posted by JPD at 11:12 AM on June 9, 2010
Well, I don't want to get too off track here, but the the discrete calculation is kind of the definition of periodic compounding, which is what the original question was about. Are you sure you aren't confusing this with continuous compounding, which is the limit of periodic compounding as your time interval approaches zero?
Honestly, I'm not sure why you'd need calculus to solve the periodic problem, or for that matter how you'd use it to solve it. You need it for the continuous problem since the answer is a solution to a differential equation.
Yes, interest rate calcs are a pain in the ass, because there's no one way to either quote or calculate interest; it all depends on the terms of the lender/borrower agreement. A particularly-annoying part of the CFA curriculum involves learning how to compare different kinds of quoted yield (e.g., A.P.Y, A.P.R., B.E.Y., mortgage rate, etc.)
posted by Opposite George at 11:44 AM on June 9, 2010
Honestly, I'm not sure why you'd need calculus to solve the periodic problem, or for that matter how you'd use it to solve it. You need it for the continuous problem since the answer is a solution to a differential equation.
Yes, interest rate calcs are a pain in the ass, because there's no one way to either quote or calculate interest; it all depends on the terms of the lender/borrower agreement. A particularly-annoying part of the CFA curriculum involves learning how to compare different kinds of quoted yield (e.g., A.P.Y, A.P.R., B.E.Y., mortgage rate, etc.)
posted by Opposite George at 11:44 AM on June 9, 2010
Best answer: Why do you say you're not looking for the future value? Based on the information you've given us, that's pretty much exactly what you're looking for.
I know it can be a little bit confusing because "present value" is discounted back to the present based on a magical rate of return, so that if someone promises you $500 in a year's time, that amount might only be worth $450 today.
However, you're already accounting for this discount rate through the interest rate - the 2% monthly interest you're receiving is an instantiation of the time value of your money sitting in the bank ready to be loaned out by the bank, as opposed to being spent by you.
posted by Phire at 11:47 AM on June 9, 2010
I know it can be a little bit confusing because "present value" is discounted back to the present based on a magical rate of return, so that if someone promises you $500 in a year's time, that amount might only be worth $450 today.
However, you're already accounting for this discount rate through the interest rate - the 2% monthly interest you're receiving is an instantiation of the time value of your money sitting in the bank ready to be loaned out by the bank, as opposed to being spent by you.
posted by Phire at 11:47 AM on June 9, 2010
You don't need calculus to solve discrete compounding problems.
You don't even need it to solve continuous compounding problems.
All you need is Excel or a financial calculator.
posted by dfriedman at 1:44 PM on June 9, 2010
You don't even need it to solve continuous compounding problems.
All you need is Excel or a financial calculator.
posted by dfriedman at 1:44 PM on June 9, 2010
2% per month compounded monthly
... is the same - numerically - as 2% per year compounded annually. So you can use a year-based calculator, just by mentally replacing "year" with "month".
posted by TruncatedTiller at 1:58 PM on June 9, 2010
... is the same - numerically - as 2% per year compounded annually. So you can use a year-based calculator, just by mentally replacing "year" with "month".
posted by TruncatedTiller at 1:58 PM on June 9, 2010
This thread is closed to new comments.
posted by donmateo at 8:38 AM on June 9, 2010