Excel and Google Sheets how-to
How long to $1,000,000? FV and NPER
Put $500 a month away at an example 7% a year. FV says what it grows to, NPER says when it passes $1,000,000. Two formulas, no calculator site.
| A | B | C | |
|---|---|---|---|
| 1 | Years | You put in | Balance |
| 2 | 10 | 60,000 | 86,542.40 |
| 3 | 20 | 120,000 | 260,463.33 |
| 4 | 30 | 180,000 | 609,985.50 |
| 5 | 40 | 240,000 | 1,312,406.70 |
Sample data. $500 a month at an example 7% a year, added monthly. Years in A, money put in (=500*12*A2) in B, the FV formula in C2 filled down.
The formula
Click C2 and type:
=FV(7%/12,A2*12,-500)C2 shows 86,542.40 after 10 years. Fill it down: 260,463.33 after 20, 609,985.50 after 30 and 1,312,406.70 after 40.
- 7%/12 is the rate for one month, because the money goes in monthly.
- A2*12 is the number of months.
- -500 is the monthly amount. It is negative because it leaves your pocket, so FV comes back positive.
After 40 years you put in 240,000 and the balance is 1,312,406.70, so 1,072,406.70 of it is interest. Format column C as currency to see the dollar signs.
When does it pass $1,000,000?
NPER counts how many months it takes to reach a target:
=NPER(7%/12,-500,0,1000000)/12That gives 36.38 years. Without the /12 it shows months: about 436.5. The parts are the monthly rate, the monthly amount (negative again), what you start with (0) and the goal.
Starting with money already saved
FV has a fourth part for a starting balance. With 10,000 already saved:
=FV(7%/12,A2*12,-500,-10000)gives 106,639.02 after 10 years instead of 86,542.40. The starting amount is negative for the same reason as the monthly one.
How much a month for a goal
Turn the question around with PMT. To reach 1,000,000 in 30 years at the same example rate:
=-PMT(7%/12,30*12,0,1000000)That gives 819.69 a month. The minus sign in front makes the answer positive.
Start at 25 or at 35
Same 500 a month, stopping at 65. From 25 that is 40 years (1,312,406.70); from 35 it is 30 years (609,985.50). The difference:
=FV(7%/12,40*12,-500)-FV(7%/12,30*12,-500)is 702,421.20, from only 60,000 less put in. The early deposits have more years to grow.
The fine print
FV and NPER assume the same rate every month and a deposit at the end of each month. Real returns go up and down, so 7% here is an example, not a promise, and this page is spreadsheet math, not financial advice. Deposits at the start of each month? Add 1 as the fifth part:
=FV(7%/12,A2*12,-500,0,1)which gives 87,047.23 after 10 years. FV, NPER and PMT work in every current version of Excel and in Google Sheets.
Watch it in 22 seconds
Templates with the math already done
Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.
- Investment Portfolio Tracker (Canada)Holdings, contributions and growth in one place.
- Savings Tracker and Sinking FundsSet goals and watch each fund fill up.
- Monthly Budget TrackerFind the 500 a month in your budget first.
More how-tos
- Find a price by its code with XLOOKUPType a product code, get its price back from your price list. One formula, no helper columns.
- Total each category with SUMIFSAdd up all the Fuel, all the Office, all the Meals: one formula per category, and more rules when you need them.
- Check two conditions at once with IF and ANDFree shipping only when the order is 50 or more and ships in Canada: one IF, one AND, done.
- Add leading zeros to numbersType 00021 and Excel or Google Sheets shows 21, because to them it is just the number 21. Here is how to get the zeros back, and keep them.
- Fix #DIV/0! in an averageAn average of nothing is a division by zero, so AVERAGE shows #DIV/0! until the first number arrives. One IF fixes it.
- Count rows that meet two conditions with COUNTIFSHow many of Ana's deals were 500 or more? COUNTIFS answers it in one cell, and takes as many rules as you need.
- Calculate percentage changeSales went from 400 to 500. That is a 25% increase, and one short formula works it out for every row.
- Calculate years of service from a start dateAna started on 15 March 2019. How many full years has she worked? DATEDIF counts them, and the same formula works out an age from a date of birth.
- Combine first and last name into one cellFirst names in one column, last names in the next, and you need "Ana Lopez" in one cell. The & sign joins them.
- Find duplicates in a list with COUNTIFA mailing list where some people signed up twice. One formula shows how many times each email appears, so the repeats stand out.
- Split a full name into first and last nameYour list has "Ana Lopez" in one cell and you need Ana and Lopez in two. Two short formulas find the space and split there.
- Total amounts by month with SUMIFSA list of dated expenses and you want one total per month. SUMIFS adds every amount between the first and the last day of the month.
- Count working days between two datesAna is off from 1 Oct to 14 Oct. How many working days is that? NETWORKDAYS counts Monday to Friday and leaves out the weekends.
- Hide #N/A in VLOOKUP with IFERRORYour order list has a code that is not in the price list, and VLOOKUP shows #N/A. IFERROR puts your own words there instead.
- Count cells that contain textA stock list where some items have a note and most do not. One COUNTIF with an asterisk counts the notes and skips the empty cells.
- Make a running total (cumulative sum)Monthly sales in one column, the total so far in the next. One formula, filled down.
- Round to 2 decimal placesA 15% discount turns 12.99 into 11.0415. ROUND makes it 11.04 and keeps your totals right to the cent.
- Free pricing calculatorsPrice a job from your real costs and margin. No sign up.