That's it! Our monthly loan amortization schedule is done:. Because a loan is paid out of your bank account, Excel functions return the payment, interest and principal as negative numbers. By default, these values are highlighted in red and enclosed in parentheses as you can see in the image above. For the Balance formulas, use subtraction instead of addition like shown in the screenshot below:. In the above example, we built a loan amortization schedule for the predefined number of payment periods.
This quick one-time solution works well for a specific loan or mortgage. If you are looking to create a reusable amortization schedule with a variable number of periods, you will have to take a more comprehensive approach described below. In the Period column, insert the maximum number of payments you are going to allow for any loan, say, from 1 to You can leverage Excel's AutoFill feature to enter a series of numbers faster.
Because you now have many excessive period numbers, you have to somehow limit the calculations to the actual number of payments for a particular loan. This can be done by wrapping each formula into an IF statement.
The logical test of the IF statement checks if the period number in the current row is less than or equal to the total number of payments. Assuming Period 1 is in row 8, enter the following formulas in the corresponding cells, and then copy them across the entire table.
As the result, you have a correctly calculated amortization schedule and a bunch of empty rows with the period numbers after the loan is paid off. If you can live with a bunch of superfluous period numbers displayed after the last payment, you can consider the work done and skip this step. If you strive for perfection, then hide all unused periods by making a conditional formatting rule that sets the font color to white for any rows after the last payment is made.
In the corresponding box, enter the below formula that checks if the period number in column A is greater than the total number of payments:.
After that, click the Format… button and pick the white font color. To view the summary information about your loan at a glance, add a couple more formulas at the top of your amortization schedule. If you have payments as positive numbers , remove the minus sign from the above formulas. Our loan amortization schedule is completed and good to go!
Download loan amortization schedule for Excel. The amortization schedules discussed in the previous examples are easy to create and follow hopefully :. However, they leave out a useful feature that many loan payers are interested in - additional payments to pay off a loan faster. In this example, we will look at how to create a loan amortization schedule with extra payments.
College tools. Family activities. Fax covers. Financial management. Food and nutrition. Fun and games. Health and fitness. Home learning. Papers and reports. Photo albums. Food and nutrition. Fun and games.
Health and fitness. Home learning. Papers and reports. Photo albums. Planners and trackers. Profit and loss. Resumes and cover letters. Social media. Fax covers. Financial management. Food and nutrition. Fun and games. Health and fitness. Home learning. Papers and reports. Photo albums.
0コメント