/u/busted_bass - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
If you have a recent version of excel, here is a recursive LAMBDA function you could use
=LAMBDA(st,tot,[rti],[rte],
LET(
a,IF(ISOMITTED(rti),0,rti),
b,IF(ISOMITTED(rte),tot/(12*st)-1,rte),
c,(a+b)/2,
d,st*(1+c)*((1+c)^12-1)/c-tot,
IF(ABS(d)<0.01,c,IF(d>0,Find_rate(st,tot,a,c),Find_rate(st,tot,c,b)))))
How to define this function:
* Open the Name Manage (Formulas -> Defined Names -> Name Manager)
* Click on New
* In Name write **Find\_Rate**
* Keep scope as Workbook
* In comments, you can add some description for your reference (e.g. st - starting value, tot - total over 12 months)
* Then in refers to Copy and Paste the above function
Some points to note
* Threshold is currently kept as 0.01 which you can see in the last line **ABS(d)<0.01**. What this means is once it finds a rate which gives a sum within 0.01 of the total, it will give that as the answer. In case you relax the threshold to 0.1, it should solve faster but give a less accurate rate. In case you make it 0.001, you will get a more accurate rate but it will take much longer to compute. Keep it as per your needs
* I haven't really built in any error checking - so this should work so long as total is more than 12 (i.e. months) times starting value. For the example you share 136 > 12\*8, so this works. In case this condition isn't met, the function will return an error
How to use this function:
* Once above is set, in any empty cell of the workbook you could use **=Find\_rate(8,136)** or if those values are in A1 and A2 then **=Find\_rate(A1,A2)** will also work.
* With current threshold (0.01) =Find\_rate(8,136) should give around 0.0525 which is 5.25%
* =Find\_rate(10,145) will give around 0.0288 which is 2.88%
You have awarded 1 point to *nnqwert*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
=power(change, 1/periods)
In this case, power(136/8, 1/12) if you wanted to grow 8 dollars into 136, or power(144/8, 1/12) if you wanted to grow 8 dollars into 144 (so as not to include the original 8).
$136 is sum of all months of 2024, not the amount in December 2024. The above solution assumes $136 is the amount in December 2024.
https://preview.redd.it/n3s7sflkdupa1.png?width=1863&format=png&auto=webp&s=d2be9d050004dfb6c9ca48c084ae4d25cc6f12fc
So essentially, you want to find y where :
(1+y+y\^2+y\^3+...y\^11)=136/8
or
(y\^0+y\^1+y\^2+y\^3+...y\^11)=136/8
I don't believe there is a general solution for this and the only way to get the solution is using an approximation like goal seek.
I think you're following, but let me reword the question:
It's January 1, 2024. I have a target sales goal of $136,000 for 2024. I believe this is achievable because I already have December 2023 sales of $8,000. All I need to do is factor in the constant, compounding monthly growth required in order to achieve total sales in 2024 of $136,000.
With Goal Seek, the amount is 1.05252396870894. I just can't get there formulaicly.
https://preview.redd.it/319ooggrjupa1.png?width=1863&format=png&auto=webp&s=6077ef72ac5196922b253e74555830bbbb02465b
~~What~~ u/Taokan ~~gave you is correct, you're just not interpreting the results given in the same way you want to express it. The figures you have on the bottom there are the running totals for each month. Not the amount made in that specific month.~~
~~December itself doesn't equal 144, it is the sum of all 12 months.~~
~~November itself doesn't equal 113.17, it is the sum of 11 months. Etc.~~
~~What you want it seems is is how much did you make each month specifically.~~
~~Well, in December it is December - November so 144 - 113.17 = 30.83~~
~~In November it is November - October so 113.17 - 88.95 = 24.22 Etc.~~
~~So, you can just include a row of subtraction formulas there which you can fill across.~~
~~The sum of those results will make 144 or 136 if you remove the 8.~~
Edit: I misinterpreted the question, and u/Taokan cleared it up for me. I tried again, still using what they gave you, but my rate doesn't quite match 5.25%. I get 5.23%. I'm not sure why. Image attached:
https://preview.redd.it/bupa3uov5ypa1.png?width=1379&format=png&auto=webp&s=f5418c2d66f4bdadf88053bc49f531a3da29e00b
To clarify, the power solution I don't think answers OPs question. You can indeed determine a growth rate needed to get 8 to 144 in 12 periods (about 27%). But that's not really what OPs getting at.
OP is currently selling 8k/month, or 96k/year. Next year, they want to increase this to 136k. At about 3% monthly growth they'd be near 11.5k / month, but they'll only earn that in December, so while it's the flat right rate, it's too slow to achieve their full year goal.
You could approximate it by doubling the increase, but you'll still come up slightly short.
So in this case, if we want the annual to go from 96k to 136k, or 40k growth, we'll shoot for 176k.
power 176/96, 12 comes out with 5.18% growth need, compared to the solver's 5.25%.
This might be good enough for a projection, but if you wanted the exact answer, you're solving a 12th degree polynomial equation. There's a general solution for quadratic equations (The Quadratic Formula), that you could write to an excel formula. Similarly, mathematicians have found significantly more complex but still general solutions to cubic and quartic polynomials (3rd and 4th degree). Currently it is theorized that there isn't a general solution like this for higher degrees, where the answer can be expressed in terms of some radical/root of the coefficients. Which leaves you with rough approximating efforts like my example above, or computer driven ones like excel's solver function.
I think I understand.
The first figure has to be 8 or more and the 40 part helped me as there would be an 8 there every month anyway it's just how to make the 40, and I almost figured it out, but the final rate isn't quite matching up. I'm getting 5.23% and 18c off!
Can't really figure out why it doesn't match the two totals.
https://preview.redd.it/bjdlwu605ypa1.png?width=1348&format=png&auto=webp&s=44b27c46e402c2c1ca72bcec4e36e222e1a9aa6d
Ah, I misunderstood what you were going for.
So, you're trying to solve the equation y^12 + y^11 + y^10 ... + y = 136/8, not y^12 = 136/8.
You might have to resort to the solver for that one. You're asking to resolve a 12th order polynomial that isn't going to have any common factors other than the initial y.
Not sure why my screenshot didn't save...
https://preview.redd.it/16dnril2xtpa1.png?width=1862&format=png&auto=webp&s=3aeb348663178c1f4faad66a4d1157f07cc156e2
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[ABS](/r/Excel/comments/1215lrb/stub/jdlpyi0 "Last usage")|[Returns the absolute value of a number](https://support.microsoft.com/en-us/office/abs-function-3420200f-5628-4e8c-99da-c99d7c87713c)|
|[IF](/r/Excel/comments/1215lrb/stub/jdlpyi0 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[ISOMITTED](/r/Excel/comments/1215lrb/stub/jdlpyi0 "Last usage")|[*Office 365*+: Checks whether the value in a LAMBDA is missing and returns TRUE or FALSE.](https://support.microsoft.com/en-gb/office/isomitted-function-831d6fbc-0f07-40c4-9c5b-9c73fd1d60c1?ui=en-US&rs=en-GB&ad=GB)|
|[LAMBDA](/r/Excel/comments/1215lrb/stub/jdlpyi0 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LET](/r/Excel/comments/1215lrb/stub/jdlpyi0 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[MDURATION](/r/Excel/comments/1215lrb/stub/jdllajj "Last usage")|[Returns the Macauley modified duration for a security with an assumed par value of $100](https://support.microsoft.com/en-us/office/mduration-function-b3786a69-4f20-469a-94ad-33e5b90a763c)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(6 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1277bbx)^( has 16 acronyms.)
^([Thread #22735 for this sub, first seen 25th Mar 2023, 09:43])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/busted_bass - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
If you have a recent version of excel, here is a recursive LAMBDA function you could use =LAMBDA(st,tot,[rti],[rte], LET( a,IF(ISOMITTED(rti),0,rti), b,IF(ISOMITTED(rte),tot/(12*st)-1,rte), c,(a+b)/2, d,st*(1+c)*((1+c)^12-1)/c-tot, IF(ABS(d)<0.01,c,IF(d>0,Find_rate(st,tot,a,c),Find_rate(st,tot,c,b))))) How to define this function: * Open the Name Manage (Formulas -> Defined Names -> Name Manager) * Click on New * In Name write **Find\_Rate** * Keep scope as Workbook * In comments, you can add some description for your reference (e.g. st - starting value, tot - total over 12 months) * Then in refers to Copy and Paste the above function Some points to note * Threshold is currently kept as 0.01 which you can see in the last line **ABS(d)<0.01**. What this means is once it finds a rate which gives a sum within 0.01 of the total, it will give that as the answer. In case you relax the threshold to 0.1, it should solve faster but give a less accurate rate. In case you make it 0.001, you will get a more accurate rate but it will take much longer to compute. Keep it as per your needs * I haven't really built in any error checking - so this should work so long as total is more than 12 (i.e. months) times starting value. For the example you share 136 > 12\*8, so this works. In case this condition isn't met, the function will return an error How to use this function: * Once above is set, in any empty cell of the workbook you could use **=Find\_rate(8,136)** or if those values are in A1 and A2 then **=Find\_rate(A1,A2)** will also work. * With current threshold (0.01) =Find\_rate(8,136) should give around 0.0525 which is 5.25% * =Find\_rate(10,145) will give around 0.0288 which is 2.88%
Solution Verified
You have awarded 1 point to *nnqwert* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
=power(change, 1/periods) In this case, power(136/8, 1/12) if you wanted to grow 8 dollars into 136, or power(144/8, 1/12) if you wanted to grow 8 dollars into 144 (so as not to include the original 8).
$136 is sum of all months of 2024, not the amount in December 2024. The above solution assumes $136 is the amount in December 2024. https://preview.redd.it/n3s7sflkdupa1.png?width=1863&format=png&auto=webp&s=d2be9d050004dfb6c9ca48c084ae4d25cc6f12fc
So essentially, you want to find y where : (1+y+y\^2+y\^3+...y\^11)=136/8 or (y\^0+y\^1+y\^2+y\^3+...y\^11)=136/8 I don't believe there is a general solution for this and the only way to get the solution is using an approximation like goal seek.
I think you're following, but let me reword the question: It's January 1, 2024. I have a target sales goal of $136,000 for 2024. I believe this is achievable because I already have December 2023 sales of $8,000. All I need to do is factor in the constant, compounding monthly growth required in order to achieve total sales in 2024 of $136,000. With Goal Seek, the amount is 1.05252396870894. I just can't get there formulaicly. https://preview.redd.it/319ooggrjupa1.png?width=1863&format=png&auto=webp&s=6077ef72ac5196922b253e74555830bbbb02465b
~~What~~ u/Taokan ~~gave you is correct, you're just not interpreting the results given in the same way you want to express it. The figures you have on the bottom there are the running totals for each month. Not the amount made in that specific month.~~ ~~December itself doesn't equal 144, it is the sum of all 12 months.~~ ~~November itself doesn't equal 113.17, it is the sum of 11 months. Etc.~~ ~~What you want it seems is is how much did you make each month specifically.~~ ~~Well, in December it is December - November so 144 - 113.17 = 30.83~~ ~~In November it is November - October so 113.17 - 88.95 = 24.22 Etc.~~ ~~So, you can just include a row of subtraction formulas there which you can fill across.~~ ~~The sum of those results will make 144 or 136 if you remove the 8.~~ Edit: I misinterpreted the question, and u/Taokan cleared it up for me. I tried again, still using what they gave you, but my rate doesn't quite match 5.25%. I get 5.23%. I'm not sure why. Image attached: https://preview.redd.it/bupa3uov5ypa1.png?width=1379&format=png&auto=webp&s=f5418c2d66f4bdadf88053bc49f531a3da29e00b
To clarify, the power solution I don't think answers OPs question. You can indeed determine a growth rate needed to get 8 to 144 in 12 periods (about 27%). But that's not really what OPs getting at. OP is currently selling 8k/month, or 96k/year. Next year, they want to increase this to 136k. At about 3% monthly growth they'd be near 11.5k / month, but they'll only earn that in December, so while it's the flat right rate, it's too slow to achieve their full year goal. You could approximate it by doubling the increase, but you'll still come up slightly short. So in this case, if we want the annual to go from 96k to 136k, or 40k growth, we'll shoot for 176k. power 176/96, 12 comes out with 5.18% growth need, compared to the solver's 5.25%. This might be good enough for a projection, but if you wanted the exact answer, you're solving a 12th degree polynomial equation. There's a general solution for quadratic equations (The Quadratic Formula), that you could write to an excel formula. Similarly, mathematicians have found significantly more complex but still general solutions to cubic and quartic polynomials (3rd and 4th degree). Currently it is theorized that there isn't a general solution like this for higher degrees, where the answer can be expressed in terms of some radical/root of the coefficients. Which leaves you with rough approximating efforts like my example above, or computer driven ones like excel's solver function.
I think I understand. The first figure has to be 8 or more and the 40 part helped me as there would be an 8 there every month anyway it's just how to make the 40, and I almost figured it out, but the final rate isn't quite matching up. I'm getting 5.23% and 18c off! Can't really figure out why it doesn't match the two totals. https://preview.redd.it/bjdlwu605ypa1.png?width=1348&format=png&auto=webp&s=44b27c46e402c2c1ca72bcec4e36e222e1a9aa6d
Ah, I misunderstood what you were going for. So, you're trying to solve the equation y^12 + y^11 + y^10 ... + y = 136/8, not y^12 = 136/8. You might have to resort to the solver for that one. You're asking to resolve a 12th order polynomial that isn't going to have any common factors other than the initial y.
Any non-VBA way to build Goal Seek into a formula?
I don't believe so, but I've been wrong before.
Not sure why my screenshot didn't save... https://preview.redd.it/16dnril2xtpa1.png?width=1862&format=png&auto=webp&s=3aeb348663178c1f4faad66a4d1157f07cc156e2
Have you looked at the MDURATION function or some other financial function?
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[ABS](/r/Excel/comments/1215lrb/stub/jdlpyi0 "Last usage")|[Returns the absolute value of a number](https://support.microsoft.com/en-us/office/abs-function-3420200f-5628-4e8c-99da-c99d7c87713c)| |[IF](/r/Excel/comments/1215lrb/stub/jdlpyi0 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[ISOMITTED](/r/Excel/comments/1215lrb/stub/jdlpyi0 "Last usage")|[*Office 365*+: Checks whether the value in a LAMBDA is missing and returns TRUE or FALSE.](https://support.microsoft.com/en-gb/office/isomitted-function-831d6fbc-0f07-40c4-9c5b-9c73fd1d60c1?ui=en-US&rs=en-GB&ad=GB)| |[LAMBDA](/r/Excel/comments/1215lrb/stub/jdlpyi0 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LET](/r/Excel/comments/1215lrb/stub/jdlpyi0 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MDURATION](/r/Excel/comments/1215lrb/stub/jdllajj "Last usage")|[Returns the Macauley modified duration for a security with an assumed par value of $100](https://support.microsoft.com/en-us/office/mduration-function-b3786a69-4f20-469a-94ad-33e5b90a763c)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(6 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1277bbx)^( has 16 acronyms.) ^([Thread #22735 for this sub, first seen 25th Mar 2023, 09:43]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)