I definitely want the last day of preceding month to be greater than first day of next month. Sorry for the confusion.
No confusion. It just seems incredulous. But so be it.
Put another way: the first day of a month must be less than the last day of the preceding month. After all, that is how it will be determined.
But how much less?!
How about if the first day of every month is the same (!)?
I can't believe you want that. But you are not offering any guidance.
And if the goal total for each month is greater than the preceding month (as in your example) and the amount for the first day of every month is the same, we can calculate a daily %change for each month such that the daily amount increases throughout the month. And the first day of each month will be less than the last day of the preceding month. (wink)
To demonstrate....
| daily change sums to monthly goal.xlsx |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL |
|---|
| 1 | | Goal | Daily Avg | Days | %Chg/day | Day 1 | Day 2 | Day 3 | Day 4 | Day 5 | Day 6 | Day 7 | Day 8 | Day 9 | Day 10 | Day 11 | Day 12 | Day 13 | Day 14 | Day 15 | Day 16 | Day 17 | Day 18 | Day 19 | Day 20 | Day 21 | Day 22 | Day 23 | Day 24 | Day 25 | Day 26 | Day 27 | Day 28 | Day 29 | Day 30 | Day 31 | TOTAL | =GOAL? |
|---|
| 2 | Jan'20 | $17,226.00 | $555.68 | 31 | 5.00% | $243.44 | $255.61 | $268.39 | $281.81 | $295.90 | $310.70 | $326.23 | $342.54 | $359.67 | $377.66 | $396.54 | $416.36 | $437.18 | $459.04 | $481.99 | $506.09 | $531.40 | $557.97 | $585.87 | $615.16 | $645.92 | $678.21 | $712.13 | $747.73 | $785.12 | $824.37 | $865.59 | $908.87 | $954.32 | $1,002.03 | $1,052.13 | $17,226.00 | TRUE |
|---|
| 3 | Feb'20 | $61,422.00 | $2,118.00 | 29 | 12.87% | $243.44 | $274.77 | $310.13 | $350.04 | $395.08 | $445.92 | $503.31 | $568.08 | $641.18 | $723.70 | $816.83 | $921.95 | $1,040.59 | $1,174.50 | $1,325.64 | $1,496.24 | $1,688.79 | $1,906.11 | $2,151.41 | $2,428.27 | $2,740.76 | $3,093.46 | $3,491.55 | $3,940.87 | $4,448.02 | $5,020.42 | $5,666.49 | $6,395.70 | $7,218.75 | | | $61,422.00 | TRUE |
|---|
| 4 | Mar'20 | $85,608.00 | $2,761.55 | 31 | 13.28% | $243.44 | $275.76 | $312.38 | $353.85 | $400.83 | $454.05 | $514.34 | $582.63 | $659.99 | $747.62 | $846.88 | $959.33 | $1,086.70 | $1,230.99 | $1,394.43 | $1,579.57 | $1,789.30 | $2,026.87 | $2,295.98 | $2,600.83 | $2,946.15 | $3,337.32 | $3,780.43 | $4,282.37 | $4,850.96 | $5,495.04 | $6,224.63 | $7,051.10 | $7,987.30 | $9,047.81 | $10,249.12 | $85,608.00 | TRUE |
|---|
| 5 | Apr'20 | $146,348.00 | $4,878.27 | 30 | 16.63% | $243.44 | $283.92 | $331.13 | $386.20 | $450.42 | $525.32 | $612.67 | $714.55 | $833.37 | $971.95 | $1,133.58 | $1,322.08 | $1,541.93 | $1,798.33 | $2,097.37 | $2,446.14 | $2,852.91 | $3,327.31 | $3,880.61 | $4,525.91 | $5,278.52 | $6,156.27 | $7,179.99 | $8,373.94 | $9,766.43 | $11,390.48 | $13,284.58 | $15,493.65 | $18,070.07 | $21,074.92 | | $146,348.00 | TRUE |
|---|
| 6 | May'20 | $252,744.00 | $8,153.03 | 31 | 18.50% | $243.44 | $288.49 | $341.87 | $405.14 | $480.11 | $568.95 | $674.23 | $799.00 | $946.86 | $1,122.07 | $1,329.71 | $1,575.77 | $1,867.37 | $2,212.92 | $2,622.42 | $3,107.70 | $3,682.78 | $4,364.28 | $5,171.89 | $6,128.94 | $7,263.10 | $8,607.13 | $10,199.88 | $12,087.36 | $14,324.12 | $16,974.79 | $20,115.97 | $23,838.42 | $28,249.71 | $33,477.31 | $39,672.27 | $252,744.00 | TRUE |
|---|
| 7 | Jun'20 | $338,587.00 | $11,286.23 | 30 | 20.81% | $243.44 | $294.10 | $355.30 | $429.24 | $518.56 | $626.47 | $756.83 | $914.33 | $1,104.59 | $1,334.45 | $1,612.15 | $1,947.63 | $2,352.92 | $2,842.55 | $3,434.08 | $4,148.69 | $5,012.02 | $6,055.00 | $7,315.02 | $8,837.24 | $10,676.23 | $12,897.91 | $15,581.91 | $18,824.44 | $22,741.72 | $27,474.17 | $33,191.43 | $40,098.42 | $48,442.72 | $58,523.44 | | $338,587.00 | TRUE |
|---|
| 8 | Jul'20 | $465,054.00 | $15,001.74 | 31 | 21.42% | $243.44 | $295.58 | $358.90 | $435.77 | $529.12 | $642.45 | $780.07 | $947.16 | $1,150.04 | $1,396.37 | $1,695.48 | $2,058.65 | $2,499.61 | $3,035.02 | $3,685.12 | $4,474.47 | $5,432.90 | $6,596.62 | $8,009.62 | $9,725.27 | $11,808.42 | $14,337.77 | $17,408.91 | $21,137.89 | $25,665.61 | $31,163.17 | $37,838.30 | $45,943.24 | $55,784.25 | $67,733.20 | $82,241.60 | $465,054.00 | TRUE |
|---|
| 9 | Aug'20 | $627,182.00 | $20,231.68 | 31 | 22.85% | $243.44 | $299.07 | $367.40 | $451.35 | $554.49 | $681.19 | $836.84 | $1,028.06 | $1,262.97 | $1,551.55 | $1,906.08 | $2,341.62 | $2,876.68 | $3,534.00 | $4,341.52 | $5,333.55 | $6,552.26 | $8,049.45 | $9,888.75 | $12,148.32 | $14,924.20 | $18,334.38 | $22,523.77 | $27,670.44 | $33,993.12 | $41,760.53 | $51,302.79 | $63,025.45 | $77,426.74 | $95,118.71 | $116,853.28 | $627,182.00 | TRUE |
|---|
| 10 | Sep'20 | $817,589.00 | $27,252.97 | 30 | 25.20% | $243.44 | $304.78 | $381.59 | $477.74 | $598.13 | $748.86 | $937.56 | $1,173.82 | $1,469.61 | $1,839.94 | $2,303.59 | $2,884.08 | $3,610.84 | $4,520.75 | $5,659.93 | $7,086.19 | $8,871.85 | $11,107.48 | $13,906.47 | $17,410.79 | $21,798.16 | $27,291.11 | $34,168.25 | $42,778.35 | $53,558.14 | $67,054.34 | $83,951.47 | $105,106.53 | $131,592.49 | $164,752.69 | | $817,589.00 | TRUE |
|---|
| 11 | Oct'20 | $1,049,464.00 | $33,853.68 | 31 | 25.32% | $243.44 | $305.07 | $382.31 | $479.10 | $600.40 | $752.41 | $942.91 | $1,181.63 | $1,480.80 | $1,855.70 | $2,325.52 | $2,914.30 | $3,652.13 | $4,576.78 | $5,735.52 | $7,187.63 | $9,007.38 | $11,287.85 | $14,145.69 | $17,727.07 | $22,215.19 | $27,839.59 | $34,887.98 | $43,720.86 | $54,790.04 | $68,661.69 | $86,045.35 | $107,830.18 | $135,130.45 | $169,342.57 | $212,216.45 | $1,049,464.00 | TRUE |
|---|
| 12 | Nov'20 | $1,302,430.00 | $43,414.33 | 30 | 27.53% | $243.44 | $310.46 | $395.93 | $504.94 | $643.96 | $821.24 | $1,047.34 | $1,335.68 | $1,703.41 | $2,172.38 | $2,770.46 | $3,533.20 | $4,505.93 | $5,746.45 | $7,328.52 | $9,346.13 | $11,919.23 | $15,200.72 | $19,385.64 | $24,722.71 | $31,529.14 | $40,209.45 | $51,279.54 | $65,397.35 | $83,401.95 | $106,363.40 | $135,646.39 | $172,991.29 | $220,617.65 | $281,356.06 | | $1,302,430.00 | TRUE |
|---|
| 13 | Dec'20 | $1,559,462.00 | $50,305.23 | 31 | 27.23% | $243.44 | $309.72 | $394.04 | $501.32 | $637.81 | $811.45 | $1,032.38 | $1,313.45 | $1,671.04 | $2,125.99 | $2,704.81 | $3,441.21 | $4,378.10 | $5,570.06 | $7,086.55 | $9,015.90 | $11,470.54 | $14,593.46 | $18,566.61 | $23,621.48 | $30,052.57 | $38,234.57 | $48,644.16 | $61,887.82 | $78,737.15 | $100,173.80 | $127,446.72 | $162,144.85 | $206,289.75 | $262,453.37 | $333,907.86 | $1,559,462.00 | TRUE |
|---|
|
|---|
Rich (BB code):
Formulas:
A2: date of the first day of each month (e.g. 1/1/2020)
D2: =DAY(EOMONTH(A2,0))
E2: enter an arbitrary daily rate of change for the first month
F2: =PMT(E2,D2,0,-B2)
G2: =IF(G$1>$D2,"",F2*(1+$E2))
AK2: =SUM(F2:AJ2)
AL2: =ROUND(AK2,2)=ROUND(B2,2)
E3: =RATE(D3,F3,0,-B3,0,E2)
F3: =F$2
You choose the first arbitrary daily %change and enter into E2 (e.g. 5%).
Ostensibly, the formula in E3 should be =RATE(D3,F3,0,-B3) . And that does work for some of column E.
But sometimes Excel RATE returns #NUM because the function needs us to provide a "guess". (Sigh.)
And in fact, that happens starting with E9.
I discovered by experimentation that the formula =RATE(D9,F9,0,-B9,0,20%) works in E9:E13.
But in general, that guess might not work for a different set of numbers. And a different guess might be needed for each line.
However, again if the goal total for each month is greater than the preceding month and the first-day amount is the same for each month, the daily %change would increase for each month if the number of days in every month were the same.
So if the Excel RATE implementation were well-behaved, we could use the %change for the previous month as the guess for current month. For example, that is E2 in the formula for E3 above.
Unfortunately, the number of days in every month is not the same. But I believe that the %change for the previous month can still be used as a guess for the current month -- again, if the Excel RATE implementation were well-behaved. That does work for you example, as I demonstrate above.
However, the Excel RATE (and IRR and XIRR) function is
not well-behaved, IMHO. I have seen instances, mostly with XIRR, where even if we provide the correct rate as a guess, accurate to 15 significant digits, the function still returns a #NUM error.
Hopefully, you will be lucky enough not to encounter those situations.
-----
Is it true that the monthly goal total will always be increasing?
If not, then I believe that the formula in F2 can be changed to =PMT(E2,D2,0,
-MIN(B2:B13)) .
However, my trick with RATE -- using the previous %change as a guess -- probably will not work all the time.
-----
Finally, I am suspicious about the increasing goal totals in column B.
Are those truly the intended total of the daily amounts for each month?
For example, the sum of the daily amounts for Feb'20 is truly $61,422.
Or are the goal totals in column B actually the cumulative amounts at the end of each month?
For example, the daily amounts for Feb'20 should sum to $44,196, which is $61,422 - $17,226.
