Microsoft KB Archive/104807

From BetaArchive Wiki

Mac Works: Creating an Amortization Schedule

PSS ID Number: Q104807 Article last modified on 10-22-1998

3.0 4.0



The information in this article applies to:

  • == Microsoft Works for the Macintosh, version 3.0 and 4.0 ==


An amortization table can be created if more information is desired than is provided by the built-in financial functions in Works. Works 3.0 also ships a template file named Mortgage Amortization with preformatted cells. Works 4.0 ships with a template named Mortgage and Loan Analysis.


The following is an example containing the necessary formulas for loan amortization, where:

  • Loan Amount is the amount of your loan.
  • Interest Rate is the interest rate of your loan.
  • = of Months is the number of months you have to pay your loan. =
  • Monthly Payment is the amount you pay each month.
  • Start Balance is your starting balance at the beginning of each period.
  • Int. for Month is the amount of interest for that month.
  • Payment is the actual amount you paid that month.
  • End Balance is your ending balance at the end of each period.

Example of Formulas for Loan Amortization

A1: Loan Amount: B1: <math display="inline">50,000 C1: D1: A2: Interest Rate: B2: 10% C2: D2: A3: # of Months: B3: 48 C3: D3: A4: Monthly Payment B4: =PMT(B2/12,B3,B1) C4: D4: A5: Start Balance B5: Int. for Month C5: Payment D5:End Balance A6: =B1 B6: =A6*(</math>B<math display="inline">2/12) C6: =</math>B<math display="inline">4 D6: =A6+B6+C6 A7: =D6 B7: =A7*(</math>B<math display="inline">2/12) C7: =</math>B<math display="inline">4 D7: =A7+B7+C7 A8: =D7 B8: =A8*(</math>B<math display="inline">2/12) C8: =</math>B$4 D8: =A8+B8+C8

Fill down cells A7:D7 for as many rows as there are periods in the loan. This procedure will create an amortization table that shows the principal balance at the start of each period, the interest paid each period, the payment, and the ending balance for each period.

If payments different from the normal monthly payment are made for some months, the actual payment amount can be entered for that month. The following interest amounts and balances will be adjusted automatically. Another column can be added to show the principal paid for the month.

The following formula will automatically show the interest paid each month on a loan:

=Payment-Int.for Month

KBCategory: kbother KBSubcategory: macworkskb

Additional reference words: 3.00 mac

Version : 3.0 4.0 Platform : MACINTOSH Issue type : kbhowto ============================================================================= Copyright Microsoft Corporation 1998.