Microsoft KB Archive/26862

Mac Works: Creating Amortization Table with a Works Spreadsheet

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

1.0 1.1 2.0 3.0 4.0

MACINTOSH

=
=========================================================

= SUMMARY =

Microsoft Works for the Macintosh versions 3.0 and 4.0 come complete with many sample documents. These documents are called Stationary documents. One of the Stationary documents is a Mortgage and Loan Analysis, complete with an amortization schedule. The file names and locations are as follows:

Works 3.0
File Name: Mortgage Amortization Location: Microsoft Works 3.0 Folder, Stationary Folder, Personal Productivity Folder

Works 4.0
File Name: Mortgage and Loan Analysis Location: Microsoft Works 4.0 Folder, AutoStart Stationary Folder, Business Folder, Finances Folder

Note: These files open as “Untitled” documents so that the original stationary or template document is preserved.

= MORE INFORMATION =

To create a loan amortization table using the Spreadsheet module in version 2.0 or earlier of Microsoft Works for the Macintosh, make a worksheet similar to the following:


 * A | B | C | D | —+—————+—————+—————+—————+ 1|Loan Amount: | $50,000| | | ---+---+---+---+---+ 2|Interest Rate: | 10%| | | ---+---+---+---+---+  3|# of Months: | 48| | | ---+---+---+---+---+  4|Monthly Payment|=PMT(B2/12,B3,B1) | | ---+---+---+---+---+  5| Start Balance |Int. for Month | Payment | End Balance | ---+---+---+---+---+  6|=B1 |=A6*($ B$2/12) |=$ B$4 |=A6+B6+C6 | ---+---+---+---+---+  7|=D6 |=A7*($ B$2/12) |=$ B$4 |=A7+B7+C7 | ---+---+---+---+---+  8|=D7 |=A8*($ B$2/12) |=$ B$4 |=A8+B8+C8 | ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

The amortization table above shows the principal balance at the start of each period, the interest paid each period, the payment, and the ending balance for each period.

To create a table that reflects the entire time span of your loan:


 * 1) Select cells A7:D7 and select as many rows down as there are payment periods in your loan.
 * 2) From the Edit menu, choose Fill Down.

If you make payments some months that are different than your normal monthly payments, the actual payment amount can be entered for that month, and the following interest amounts and balances will be adjusted automatically. To show the principal paid for the month, add an additional column that contains the following formula

=“Payment”-“Int. for Month”

where “Payment” is the amount paid, and “Int. for Month” is the interest paid during the month.

KBCategory: kbusage KBSubcategory: macworkskb

Additional reference words: 1.x 2.0 1.00 1.10 2.00 3.00 4.00 schedule mswksss tmpl amort ====================================================================== Version : 1.0 1.1 2.0 3.0 4.0 Platform : MACINTOSH Issue type : kbinfo ============================================================================= Copyright Microsoft Corporation 1998.