Financial › NPER, ROUNDUP121 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeNPERROUNDUP

NPER: months needed to save a house down payment

Salma wants to collect Rp60,000,000 for a house down payment. She already has Rp10,000,000 and can save Rp2,000,000 every month in an account paying 4% a year.

Task

In B6, calculate how many months it takes with NPER: the monthly rate from B1, the monthly deposit in B2, the starting savings in B3 and the target in B4. Round up to whole months with ROUNDUP.

Cells to fillB6

Required functionsNPERROUNDUP

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
NPER(rate, pmt, pv, fv): the deposit (pmt) and starting savings (pv) are money you put in, so they are negative. The target (fv) is money you get back later, so it is positive.
Hint 2
The monthly rate is B1/12. NPER usually returns a fraction, such as 23.7 months.
Hint 3
Pattern: =ROUNDUP(NPER(…), 0). Make sure the minus signs are on the deposit and the starting savings, not on the target.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

B6

=ROUNDUP(NPER(B1/12,-B2,-B3,B4),0)

Explanation

=ROUNDUP(NPER(B1/12,-B2,-B3,B4),0) gives 24 months. The raw NPER result is about 23.7, and it is rounded up because 23 months are not quite enough. If all signs are the same (for example all positive), Excel returns #NUM! because the cash flow makes no sense: money keeps coming in and nothing is ever paid in. Use ROUNDUP, not ROUND, so you do not fall slightly short in the last month.

Starting worksheet: Sheet1
Row	A	B
1	Annual interest	4%
2	Monthly deposit	2,000,000
3	Starting savings	10,000,000
4	Down payment target	60,000,000
5
6	Months needed
7
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result