To find the monthly saving for a goal, use =-PMT(annual rate/12, months, 0, target). Say you want ₹3,00,000 in 24 months and expect nothing in interest: the answer is ₹12,500 a month, which is just target divided by months. With an assumed 6% a year the same goal needs about ₹11,796 a month.
The PMT arguments, one at a time
The syntax is =PMT(rate, nper, pv, [fv], [type]). For a savings goal:
- rate: the assumed yearly return divided by 12, for example B2/12.
- nper: months left until the goal date.
- pv: money you already have set aside for it, entered as a negative number (it is money you have put in). Use 0 for a fresh start.
- fv: the target amount, as a positive number.
- type: 0 if you save at the end of each month, 1 if at the start.
Excel treats outgoing money as negative, so PMT returns a negative number for a deposit. The minus sign in front of PMT turns it back into a plain positive amount. The formula works the same in Google Sheets.
Setting up the sheet
- B1 Target amount: 300000.
- B2 Goal date: type a date.
- B3 Assumed return per year: 6%, or 0% if you will keep it in a drawer.
- B4 Already saved: 0 or 50000.
- B5 Months left: =(YEAR(B2)-YEAR(TODAY()))*12+MONTH(B2)-MONTH(TODAY()).
- B6 Monthly saving needed: =-PMT(B3/12, B5, -B4, B1).
Zero interest: when is the shortcut enough?
If the money sits in a drawer, a wallet or a savings account paying almost nothing, skip the function and use =(B1-B4)/B5. With a short horizon of a year or less, the interest earned is small enough that the shortcut and PMT differ by a small amount. For longer goals or higher assumed rates, PMT is better because it credits the interest your earlier deposits earn.
| Months | Monthly at 0% | Monthly at an assumed 6% | Total difference over the period |
|---|---|---|---|
| 12 | ₹25,000 | ₹24,320 | ₹8,161 |
| 24 | ₹12,500 | ₹11,796 | ₹16,892 |
| 36 | ₹8,333 | ₹7,627 | ₹25,443 |
With ₹3,00,000 as the target, the longer the period, the more the assumed interest does. After 36 months the 6% case asks for ₹707 a month less than the 0% case, though only if the 6% actually happens.
What if I already have some money saved?
Put it in B4. With ₹50,000 already saved, the 24-month goal at an assumed 6% needs about ₹9,580 a month, against ₹11,796 from a standing start. Without any interest it is (300000-50000)/24 = ₹10,417. The formula does the growth of the existing balance for you.
If you will save on salary day, at the start of each month, add the type argument: =-PMT(B3/12, B5, -B4, B1, 1). Each deposit then earns one extra month of the assumed interest, so the monthly amount is slightly lower. With a 0% assumption, the two versions are identical.
Does the answer change if the goal date moves?
Yes, immediately: months left is calculated from today, so every day closer to the date changes it. If you save less one month, enter the actual balance in B4 and let the sheet recompute the remaining monthly amount, which spreads the shortfall over the months left.
Quick checks before you start the standing instruction
- Months left is a positive whole number and not zero.
- The target is in B1 as a positive number and the existing balance has the minus sign inside the formula.
- The monthly figure times months, plus the existing balance, is at least the target.
- The amount fits your monthly budget with room for irregular costs.
To see what you can actually set aside, look at the last three months of statements. SpendixAI can import them, categorise the rows for you to review, and show how much was left after spending, which is a better basis for the monthly figure than a guess.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


