Question:

I need an excel formula dealing with amortization?

by  |  earlier

0 LIKES UnLike

I have an amortization schedule and i want excel to determine when the balance is zero and input the payment number into a cell. Is there a formula for that?

 Tags:

   Report

4 ANSWERS


  1. Have you checked Excel's help page?


  2. To complete in Excel, do this:

    In cell A1, enter your total loan amount

    In cell A2, enter your rate (note: MONTHLY rate, not annual)

    In cell A3, enter your monthly payment (enter as a negative value)

    In cell A4, enter:

    =NPER(A2,A3,A1)

    This will show you the number of payments to pay off, or in other words until the balance is zero.

    Keith

  3. Here's the equation to determine what your payment value should be:



    P=i/(1-(1+i)^(-n)) x principle

    where:

    P = payment

    i = interest per payment period

    n = number of payment periods

    example:

    if yearly interest = 9% amortized monthly, and there's 60 months then,

    i = 9%/12 = 0.0075

    n = 60

  4. Instead of that go to google.  Type in Amortization calculator and find the first one that has hsh website.  

    Then you can type in your information for the loan and it will give you the amortization calculator and will also help you figure out additional principal payments, etc.

Question Stats

Latest activity: earlier.
This question has 4 answers.

BECOME A GUIDE

Share your knowledge and help people by answering questions.
Unanswered Questions