How to Create an Amortization Schedule in Excel and Smartsheet

Large loans like mortgages and tuition are often paid off over decades, and they can be difficult to manage on your own. There are several programs to help you stay on top of your loan payments - from in-person consulting services to pre-built loan calculator tools - but adhering to a schedule is another way to ensure proper payback.

This article will explain the intent and importance of a loan payment calendar, called an amortization schedule. Then, we’ll provide an in-depth tutorial for creating your own schedule in Excel, as well as another powerful online tool, Smartsheet.

What is an Amortization Schedule?

Loans are typically paid off over many years, and accrue interest during this time. The process of paying down loans in small increments over time is called “amortization.” Large loans with long payment periods (i.e. mortgages) can feel overwhelming, so it can be helpful to make a plan of payment - this is called an amortization schedule. Amortization schedules will help you stay on top of your current balances, and also enable you to look ahead to the future because you know exactly how much you will owe, and when it is due. 

Before creating an amortization schedule, there are several pieces of information about your loan that you will need. These variables will enable you to calculate the necessary payments and balance(s):

  • Loan amount: This is the total dollar amount of the loan you received (before it has accrued interest or been paid down).
  • Annual interest rate: The yearly interest applied to your initial loan.
  • Loan period: The total amount of time you will take to pay off your loan. This is typically in years.
  • Payment frequency: How often you will make payments on your loan. This will typically be monthly, though it could also be weekly or yearly depending on the type of loan you’ve taken out.
  • Number of payments: The total number of payments you will make to pay off the loan. Some tools will calculate this value for you by multiplying loan period by payment frequency.
  • Loan date: The date that the loan was disbursed.
  • First payment due date: The date of the first required payment.
  • Payment amount: This is the amount you will pay each payment period (monthly or yearly). You will calculate this value based on the other factors. Payments are comprised of the principal (loan money you are paying back) plus the interest.
  • Payoff amount: The total amount of money that you will have paid by the end of the loan period. This is the starting loan amount plus the total amount of interest accrued over time.

Many people opt for a calculator tool to find these values, but mapping out a full schedule helps you visualize and organize your payments. You can create an amortization schedule from scratch using a basic spreadsheet or calendar, but there are several online tools with built-in amortization schedule templates to help you get started. In the next section, we’ll walk you through setting up an amortization schedule in two premiere programs, Excel and Smartsheet.

How to Create an Amortization Schedule in Excel

In this section, we’ll show you how to create an amortization schedule using Excel’s built-in template, “Loan Amortization Schedule.”

Step One: Input your starting data and create the schedule.

1.  Open Excel and select “Loan Amortization Schedule” from the template gallery. A blank spreadsheet will open with fields to list your starting values.

 

2.  Input your starting data. On the left-hand side, there are fields for Loan amount, Annual interest rate, Loan period in years, Number of payments per year, and Start date of loan. You should be able to find all of these values within your loan agreement.

 

3.  With this information, Excel will automatically calculate the loan payments (Scheduled payment) through the end of the payment period.

4.  Additionally, the Excel sheet will auto-populate values for Principal, Interest, Ending balance, and Cumulative Interest for each payment date. These values help you better understand the composition of each payment, as well as stay informed about future payments.

 

Step Two: Refine and edit your amortization schedule.

1.  Many loan payoff schedules change along the way. In Excel, you can modify your schedule by simply adjusting the numbers in the Enter Values section.

2.  For recurring constant payments (i.e. payments not correlated with a change rate), add a value to the Optional extra payments field. This is an unchanging value that will be added to each of your monthly payments. In this example, we will pay an additional $100.00 every month; Excel updates the values in the schedule accordingly.

3.  Throughout your loan period, refer to the Loan Summary section in the top right of the Excel sheet. This will give you a high-level summary of the loan.

 

Step Three: Share your schedule with stakeholders by saving it to your computer and emailing it out. Alternatively, you may upload it to OneDrive so that others can receive live updates. 

With its built-in formulas and long-term scheduling capabilities, Excel makes it easy to create an amortization schedule. However, there are other online tools that can make sharing and collaborating even easier. Next, we’ll look at one of these programs, Smartsheet.

How to Create an Amortization Schedule in Smartsheet

Smartsheet is a cloud-based, spreadsheet-inspired task and work management program. You can also use Smartsheet to create an amortization schedule using the pre-built template, “Loan Amortization Schedule.”

Step One: Input your data and create your schedule.

1.  From the Home tab in Smartsheet, select Loan Amortization Schedule from the template gallery. Click Use Template.

 

2.  A blank sheet will open with fields to input your starting loan values.

 

3.  Input values for Loan amount, Annual interest rate, and Length of loan (loan period). Again, you should have all of these starting values within your loan agreement.

 

4.  Based on underlying formulas, the sheet will calculate and auto-populate values for beginning balance, total payment, interest and principal payments, and the end balance.

5. The Calculated Output section on the left-hand side serves as the “loan summary” in the Excel template. Smartsheet calculates Monthly Interest Rate based on the annual interest rate and loan period, states the total number of payments, and also calculates Total Interest accrued during the loan period. Refer to this section throughout your payoff period and edit if necessary.

 

Step Two: Collaborate in Smartsheet

One of the greatest advantages of using Smartsheet for your amortization schedule is its powerful collaboration capability. If you have multiple loan signees or stakeholders, sharing and communicating within the sheet is easy. 

1.  Share your sheet with an infinite number of collaborators by clicking the Sharing icon at the bottom of the sheet. Type the emails of the people you want to share your sheet to, and adjust the permission levels as appropriate. Click Share Sheet. (*Note: you can share your sheet with anyone, even if they do not have a Smartsheet account.)

 

2.  Attach relevant documents, such as loan quotes and contracts, directly to your sheet by clicking the attachment icon on the left-hand side of each row. You can upload documents from your computer or from other cloud services, such as Google Drive, Box, and Dropbox.


3.  Use the Comments feature to log discussions between stakeholders. This is helpful to store all communication directly in the sheet, rather than referring to outside email chains or in-person meetings.


4.  Set reminders so you never miss a payment. Click the dropdown menu on the left-hand side of a row and select Set Reminder. Then, select a reminder date from the calendar and leave an optional message.

     


5.  Stay up to date with all changes via Smartsheet’s mobile app, available for iPhone, iPad, and Android.

Ultimately, Smartsheet is easy to use and provides all the built-in formulas and scheduling functionality of Excel. Additionally, explore the collaboration features and stay on top of your loan with live updates and anywhere, anytime access.

Understanding Amortization Formulas

While the built-in templates in Excel and Smartsheet greatly simplify the process of creating an amortization schedule, it is also important to understand the underlying formulas. This will help you to cross-check your schedule for accuracy, and enable you to calculate the values manually should you ever need to. In this section, we’ll break down the key functions and offer a few tips to help you ensure accuracy in your schedule. 

There are three main formulas used to calculate payments in an amortization schedule - PMT(), PPMT(), and IPMT(). Learning them will help you break down each payment:

  • The PMT() function calculates total monthly payment. It is defined as (Interest Rate,NPer,PV,FV), where NPer is the total number of periods, PV is the principal of the loan, and FV is the the future value or outstanding value after all payments have been made. (*Note: the FV value is optional; if there won’t be any outstanding payments, FV is assumed as 0.)
  • The PPMT() function returns the principal amount on any particular monthly payment. This is the amount you are paying back of the original loan, not including interest. The function is defined as PPMT(Interest Rate,Period,NPer,PV,FV).
  • The IPMT() function returns the interest payment for any given monthly payment. It is defined as IPMT(Interest Rate,Period,NPer,FV), where Period is the monthly period you calculating the interest for. Therefore, the Period value will be a value between 1 and NPer (the total number of payments).

If you choose, you can always manually calculate these values. Even if you rely on the Excel or Smartsheet automatic functions, it’s a good idea to periodically calculate the payment amount(s) yourself to cross-check for accuracy, or in the event of changing loan information. 

Additional Amortization Scenarios

The above examples illustrate a typical, 30-year payback schedule with a fixed interest rate. However, some loans do not follow these criteria, and you will need to take other factors into consideration when creating your schedule. Below are some explanations and tips for managing a loan that deviates from the standard model.

  • Fixed vs. adjustable-rate loans: In a fixed-rate loan, the interest rate will stay constant throughout the loan period. By contrast, an adjustable-rate loan may increase or decrease in interest over time. If you have an adjustable-rate loan, be sure that your schedule reflects this so you don’t end up owing interest at the end of the period.
  • Negative amortization: If your monthly payments fail to cover the interest due, there will be an increase in your principal balance to make up for the lost interest. The remaining amount of interest owed is added to the principal loan. To combat this possibility of larger payments or more money borrowed, plan to pay each installment in full (including interest).
  • Balloon loan: If a loan is not fully amortized by the end of the loan period, there will be a leftover balance. This is called a “balloon payment” because it is typically a much larger payment than the earlier, periodic payments.

Discover a More Collaborative Amortization Tool with Smartsheet

Smartsheet is a cloud-based work management platform in a familiar spreadsheet layout. You can create an amortization schedule in Smartsheet using the pre-built “Loan Amortization Schedule” template, and modify the sheet to fit the specific needs of your payment plan. 

You can use the template to easily visualize and track your monthly loan payments. The amortization table calculates the beginning balance, total payment, interest and principal payments, and end balance for each period. Rather than manually calculating your loan information, Smartsheet’s built-in formulas create a seamless, accurate schedule that you can follow to stay on top of your payments. Of course, you can edit the template to suit your specific needs - all changes will be updated in real time to ensure that there is one record of the truth.

Additionally, Smartsheet’s powerful collaboration features give outside stakeholders insight into the payment schedule, and allow multiple editors to make changes and check for accuracy. Attach relevant documents directly to the sheet, or copy emails and add notes in Comments. Share your sheet with an infinite number of collaborators and specify editing permissions, and set alerts and reminders so you never miss a payment. 

Smartsheet makes payment scheduling easy with anytime, anywhere access - get automatic updates from team members and access your information on the go with the mobile app. 

Create Your Loan Amortization Schedule in Smartsheet

Add new comment

Our Privacy Policy describes how we process your personal data.

By submitting this form, you accept the Akismet privacy policy.