zumpoof
asked on
How do I calculate an increasing monthly spend rate given a yearly budget and a starting value?
I have an annual budget of $1,000. I'd like to spend $20 in January and increase spending each month evenly. By the end of the year I want to have spent the full $1,000.
I'm looking for a formula that I can type into Excel/GoogleSheets that will allow me to enter a $20 in one cell, $1,000 in another, and then automatically populate 12 monthly cells w/ what the monthly budget is for each. Each month should be more than the previous month.
I'm looking for a formula that I can type into Excel/GoogleSheets that will allow me to enter a $20 in one cell, $1,000 in another, and then automatically populate 12 monthly cells w/ what the monthly budget is for each. Each month should be more than the previous month.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Zumpoof,
This is a pretty common requirement that we get from clients.
I have attached a file showing how to do it, with an increase in the budget each month evenly.
There are two versions included:
The first is more generic, and will work with any number of periods. However, it is necessarily more complicated, since it 'counts' the number of periods within the formula.
The second is simpler, and will work if you are always working with 12 periods (months) as per your question, plus it references cells, rather than columns, so is often easier to understand.
Hope that helps,
Alan.
EE-29158034-IncreasingBudget-Version.xls
This is a pretty common requirement that we get from clients.
I have attached a file showing how to do it, with an increase in the budget each month evenly.
There are two versions included:
The first is more generic, and will work with any number of periods. However, it is necessarily more complicated, since it 'counts' the number of periods within the formula.
The second is simpler, and will work if you are always working with 12 periods (months) as per your question, plus it references cells, rather than columns, so is often easier to understand.
Hope that helps,
Alan.
EE-29158034-IncreasingBudget-Version.xls
Alan's second option (the simpler one) is basically the same as my suggestion, ie base amount plus the remainder divided by 66 times the number of periods.
Its just the basic maths that we all learnt in school of course - how to calculate triangular numbers = N*(N+1)/2 in Excelish :-)
Alan.
Alan.
ASKER
Sorry for the late reply on this. Thank you so much for the help Rob, it worked great!
Likewise there is probably an investment function whereby you are starting with nothing and paying a minimum amount in and accruing interest on each payment to achieve a total at the end of the period.
I will keep looking but my Office suite has just decided to do an update.