How to Create an Amortization Schedule in Excel and Smartsheet

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.

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.

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.

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. 

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.

Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change. 

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed. 

When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time. Try Smartsheet for free, today.