How much to save each month in Excel

The monthly deposit for a goal, starting from zero

PMT takes four arguments here: the interest rate per period, the number of periods, what you have now (present value) and what you want to end with (future value). Savings accounts quote an annual rate but you deposit monthly, so divide the rate by 12 and count the term in months.

This plan is a $12,000 house deposit in 24 months, in an account paying 4.5% a year. Present value is 0 because you start with nothing.

Why PMT gives a negative number (the sign convention)

Excel's finance functions see every amount as cash moving in or out of your pocket. Money leaving you is negative, money coming back is positive. A goal of +12,000 means "I receive 12,000 at the end", so Excel answers that you must pay out 478.77 a month, shown as a negative.

Neither answer is wrong. Pick one habit and stick to it: either enter the goal as negative to get a positive deposit (as above), or keep the goal positive and put a minus in front of PMT.

Include what you have already saved

Money already in the account also earns interest, so it lowers the deposit by more than its face value. Put it in the present value argument. Using real-world signs, the 2,500 you put in is negative and the 12,000 you get back is positive. The minus in front flips the deposit to a positive number for display.

Check an existing plan with FV

Already saving a fixed amount? FV tells you where you will end up. Here the deposits are 400 a month and the starting balance is 2,500. Both are money you pay in, so both are negative, and FV returns a positive balance.

Subtract the goal from the result to see the gap: =FV(B3/12,B4,-B6,-B5)-B2. A positive number means you finish ahead of the goal.

Quick check without interest

For short goals or a 0% current account, plain division is fine and easy to explain to someone else. Compare it with the PMT answer: the gap is what interest is worth to you.

Depositing at the start of each month, and "how many months?"

PMT assumes deposits at the end of each month. If you pay in on the 1st, add a fifth argument of 1 (type): =PMT(B3/12,B4,-B5,B2,1). Each deposit then earns one extra month of interest, so the amount drops slightly.

To flip the question and find how long a fixed deposit takes, Excel has NPER: =NPER(B3/12,-B6,-B5,B2) returns the number of months for 400 a month from a 2,500 start. NPER usually returns a fraction of a month, so round it up with ROUNDUP: you cannot make part of a deposit.