Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 96
Default Number of payments remaining

I need a formula to calculate the number of payments remaining on a loan
based on constant interest, constant payments and a known balance. I am
trying to use the NPER function but it doesn't give the right answer if I
change the interest rate. How many payments do I have left on $16,000, with
5% interest, and $250 a month payment?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Number of payments remaining

On Thu, 27 Sep 2007 12:29:01 -0700, Erik
wrote:

I need a formula to calculate the number of payments remaining on a loan
based on constant interest, constant payments and a known balance. I am
trying to use the NPER function but it doesn't give the right answer if I
change the interest rate. How many payments do I have left on $16,000, with
5% interest, and $250 a month payment?



74.59215279

Or 74 Payments plus a balloon of about $147.55

Formula:

=NPER(0.05/12,250,-16000)




--ron
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 96
Default Number of payments remaining

Thanks, that worked great, I wasn't putting the negative infront of the
balance number which gave me a differnt number and changing it to a negative
after the fact messed it up worse. Thanks for your help, worked great.

"Ron Rosenfeld" wrote:

On Thu, 27 Sep 2007 12:29:01 -0700, Erik
wrote:

I need a formula to calculate the number of payments remaining on a loan
based on constant interest, constant payments and a known balance. I am
trying to use the NPER function but it doesn't give the right answer if I
change the interest rate. How many payments do I have left on $16,000, with
5% interest, and $250 a month payment?



74.59215279

Or 74 Payments plus a balloon of about $147.55

Formula:

=NPER(0.05/12,250,-16000)




--ron

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Number of payments remaining

On Thu, 27 Sep 2007 13:36:01 -0700, Erik
wrote:

Thanks, that worked great, I wasn't putting the negative infront of the
balance number which gave me a differnt number and changing it to a negative
after the fact messed it up worse. Thanks for your help, worked great.


Changing it to a negative "after the fact" shouldn't have messed things up. I
would have those values (250, 16000, 5%) in separate cells, and refer to them
that way.

But glad to help and thanks for the feedback. It is important with Excel's
financial functions to use the signs appropriately.
--ron
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Number of payments remaining

I hope you're still here, because I needed help with the same equation.
However, I need to know how to get the dollar amount that is left on the
loan. I need to keep a running total of the balance I owe on $9016.88.
$261.08/mo. w/11.08333 mo. left @ 17.95% APR

"Ron Rosenfeld" wrote:

On Thu, 27 Sep 2007 12:29:01 -0700, Erik
wrote:

I need a formula to calculate the number of payments remaining on a loan
based on constant interest, constant payments and a known balance. I am
trying to use the NPER function but it doesn't give the right answer if I
change the interest rate. How many payments do I have left on $16,000, with
5% interest, and $250 a month payment?



74.59215279

Or 74 Payments plus a balloon of about $147.55

Formula:

=NPER(0.05/12,250,-16000)




--ron

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Constant loan payments vs. constant payments of principal lalli945 Excel Worksheet Functions 3 December 20th 06 11:33 PM
Deduct until last positive number remaining krismtx Excel Discussion (Misc queries) 1 April 26th 06 03:36 PM
Getting remaining entries harvindersingh1 Excel Discussion (Misc queries) 1 April 21st 06 03:32 PM
How do I forecast future payments by analyzing past payments? CeeBee Excel Worksheet Functions 1 March 17th 05 07:43 PM
Points remaining formula jbsand1001 Excel Worksheet Functions 0 January 5th 05 11:55 PM


All times are GMT +1. The time now is 09:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"