NPER Calculation (Incorrect Answer)

D

DickT

New Member
Joined
Jun 22, 2011
Messages
1
Dear All

I want to calculate the remaining period (in months) of a EUR10,000 loan where I have already paid EUR1,000. The interest rate is 12% and my monthly payments are EUR100 at the beginning of each month. This is in fact the example that can be found on MS Excel 2003 and 2007 for the NPER function.

The formula is:

=NPER(12%/12,-100,-1000,10000,1)

The result is 59.67 that is, almost 60 months.

The problem is this answer. If I am going to pay the remaining EUR9,000 over 60months at EUR100 this would work out as EUR6,000. A difference of EUR 3,000.

An assistance anyone can provide will be much appreciated.

Regards
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Welcome to the forums!

The formula you provided is, in fact, from the help files. However, it does not model your situation.

What you have is a Loan of $9000 that you are paying $100 on each month, and a 12% annual interest is being charged to that loan each year. It will actually take ~223 months to pay off that loan.

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Loan Amt</TD><TD style="TEXT-ALIGN: right">9000</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>Payment Amt</TD><TD style="TEXT-ALIGN: right">100</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Interest Rate</TD><TD style="TEXT-ALIGN: right">12%</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>N Payments</TD><TD style="TEXT-ALIGN: right">222.8293</TD></TR></TBODY></TABLE>
Sheet1​


<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B5</TH><TD style="TEXT-ALIGN: left">=NPER(B3/12,B2,-B1,0,1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


The problem is that you are barely paying anything on the principal, because the interest rate is so high. Here is the first 10 rows of an amortization table which illustrates how slow the balance goes down:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Period</TD><TD>Payment</TD><TD>Balance</TD><TD>Interest Accrued</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">-9000</TD><TD style="TEXT-ALIGN: right">-89</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">-8989</TD><TD style="TEXT-ALIGN: right">-88.89</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">-8977.89</TD><TD style="TEXT-ALIGN: right">-88.7789</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">-8966.67</TD><TD style="TEXT-ALIGN: right">-88.666689</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">-8955.34</TD><TD style="TEXT-ALIGN: right">-88.55335589</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">-8943.89</TD><TD style="TEXT-ALIGN: right">-88.43888945</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">-8932.33</TD><TD style="TEXT-ALIGN: right">-88.32327834</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">-8920.65</TD><TD style="TEXT-ALIGN: right">-88.20651113</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">-8908.86</TD><TD style="TEXT-ALIGN: right">-88.08857624</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">-8896.95</TD><TD style="TEXT-ALIGN: right">-87.969462</TD></TR></TBODY></TABLE>
Sheet1​
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
IPMT formula help
Replies
4
Views
1K
Dave Patton
D
Jyggalag
  • Question Question
Debt payment in Excel?
Replies
1
Views
226
Jyggalag
Jyggalag
J
  • Question Question
Actual loan rate with fees calculation
Replies
6
Views
573
Dave Patton
D
A
  • Question Question
Calculate Optimum Duration in Loan
Replies
1
Views
634
anuradhagrewal
A
D
  • Solved
Calculating the total interest paid on a car lease
Replies
4
Views
387
Domenic
D

Forum statistics

Threads
1,224,594
Messages
6,179,794
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top