Advanced Mortgage Amortization Schedule – Custom GPT by A.CRE

At A.CRE, we’ve built dozens of custom GPTs for internal and external purposes. Some of the GPTs have general application, while others are custom GPTs specifically for commercial real estate. One such CRE-specific GPT that we recently built and that I’m particularly excited to share today is our first real estate analyst GPT: our Advanced Mortgage Amortization Schedule custom GPT.

Created by using my Advanced Amortization Schedule Excel Model, this GPT has been trained to take inputs the user provides and update that Excel model accordingly. It is also trained to provide the user guidance on inputs and outputs in the model. In the coming months, we’ll release other custom GPTs that will perform most of the financial modeling for the user in this same way.

• Note: As of April 2024, a paid ChatGPT membership is required to use custom real estate GPTs like this Advanced Mortgage Amortization Schedule GPT.

Why we built the Advanced Mortgage Amortization Schedule GPT?

As mentioned, this tool marks the first in what will be a series of real estate analyst GPTs designed to help those less technically versed in using A.CRE’s real estate Excel models. Essentially, the GPT guides users through the necessary inputs, answers their questions about the model’s inputs and outputs, and updates the Excel file accordingly.

In essence, the GPT serves as a personal real estate analyst, modeling mortgage amortization schedules for users.

We also developed this GPT as a pilot project for future, more sophisticated real estate analyst GPTs aimed at modeling real estate deals. It provided valuable insights into the capabilities and limits of GPT-4 (the version on which this tool is based) and allowed us to explore innovative ways to maximize the GPT’s utility. Just as it took me three attempts to perfect my first All-in-One model without breaking Excel, this GPT has been instrumental in understanding the current boundaries of LLMs when applied to real estate analysis.

Access the Advanced Mortgage Amortization Schedule GPT

We developed this custom GPT as a service to A.CRE readers and the industry. OpenAI currently only makes custom GPTs accessible to paid ChatGPT Plus, Teams, and Enterprise users.

We regularly update the GPT (see version notes).

Click to chat with the Advanced Mortgage Amortization Schedule GPT and then use a simple prompt such as “Help me create an amortization schedule” to get started. Note that as with all generative AI applications and Excel models, there may be output mistakes.

User’s Guide – Advanced Mortgage Amortization Schedule GPT

To use the Advanced Mortgage Amortization Schedule GPT effectively, follow these steps:

1. Get Started. Enter a simple prompt to get started such as ‘Help me create an amortization schedule’ or ‘Please create an amortization for the deal in this offering memorandum’.
2. Provide Inputs. Once you start the GPT, it will guide you through the inputs it needs to create the model. Either answer its questions or provide it a PDF of an offering or financing memorandum that contains the mortgage assumptions. If you have questions about any input it asks for, simply ask it for clarification.
3. Confirm Inputs. After you’ve provided the necessary inputs, the GPT will confirm with you that the inputs are correct. Either ask it to make changes, or confirm the inputs are correct.
4. Download the finished Excel model. Once you confirm the inputs, the GPT will take those inputs, update the Excel file, and then provide you with a link to download the Excel file. Click that link, open the file, click ‘Enable Editing’, and then refer to the ‘Amortization’ worksheet.
5. Ask it Questions and/or report bugs. If you have questions about any of the inputs or outputs in the model, just ask. It has been trained on the model. If you run into a bug, let it know and it will direct you to where you can report the bug to us.

Inputs You’ll Need to Provide the Advanced Mortgage Amortization Schedule GPT

To prepare you for your conversation with this GPT, below are the inputs that the AI will ask you for. Have these inputs ready to share with the AI. If you’re unclear on what to enter, ask the GPT.

1. New Loan vs. Assumption Loan: Specify whether the scenario is a new loan or an assumption of an existing loan, which determines if you’ll need to enter an existing monthly payment.
2. Lender Name: Enter the name of the lender providing the loan, useful for identifying the loan in analyses or presentations.
3. Interest Calc Method: Choose the method for calculating interest (30/360, Actual/Actual, Actual/360, Actual/365), affecting how interest accrues over time.
4. Loan Type: Select the type of lender (e.g., Bank, CMBS, LifeCo, Private, Other), which can influence the terms and conditions of the loan.
5. Rate Type: Decide between a fixed or variable interest rate, which impacts how interest is calculated and the need for benchmark rates.
6. Analysis Start: Enter the start date for the loan analysis, setting the timeline for loan repayments and interest calculations.
7. Loan Amount: Input the principal amount of the loan, which is the basis for calculating monthly payments and interest.
8. Loan Fee (% of Loan): Specify the fee charged by the lender as a percentage of the loan amount, important for understanding the upfront costs of the loan.
9. Annual Rate (%): Enter the interest rate as a percentage, central to calculating the cost of borrowing.
10. Amortization (In Months): Define the number of months over which the loan will be paid off, essential for determining the payment schedule.
11. Interest-Only Period (In Months): Specify if there is an interest-only period and its duration, during which only interest payments will be made.
12. Term (In Months): Input the total duration of the loan in months, critical for scheduling the full repayment.
13. Payment: Choose between static or dynamic debt service payments, affecting how payments are recalculated over the loan term.
14. Monthly Payment: Enter the monthly payment amount if assuming an existing loan, necessary for continuing the existing repayment schedule.
15. Benchmark Rates: If the rate type is variable, specify the benchmark rates for adjusting the interest rate, essential for monthly payment recalculations.

Tips for Using Real Estate Analyst GPTs Such as this Advanced Mortgage Amortization Schedule

As this is likely the first custom GPT of this type you’ve used, I thought I’d share a few tips I’ve found helpful when working with this type of AI.

• Treat the AI like your analyst: Engage with the AI as if it’s part of your real estate analyst team, expecting the same level of detail and engagement you would from a human analyst.
• Ask for clarification: If results aren’t clear, don’t hesitate to ask the AI for further explanation to ensure understanding.
• Be explicit with your requests: Clearly articulate your needs; precise questions yield precise answers.
• Provide exact figures: Input precise data (e.g., loan amount, interest rate, term) to get the most accurate amortization schedules from the AI. Garbage in, garbage out, as they say!
• Double-check the outputs: Always verify the AI’s calculations against your own to ensure accuracy before making any financial decisions.

Video Walkthrough – Advanced Mortgage Amortization Schedule GPT

To further help you get started using this custom GPT, below find a video walkthrough. The video walks you through how to use the GPT to create an advanced mortgage amortization schedule in Excel. This video was built using v1.1 of the tool. We regularly update our Excel models and AI tools, and OpenAI is constantly updating ChatGPT. So, the version in the video may look different but nonetheless the base functionality is the same.

---

Version Notes

v1.1

• Changes to Excel file
  • Removed merged input cells to help GPT
  • Misc updates to version tab
  • Renamed model
• Changes to GPT instructions
  • Added guidance for proper file naming in order to avoid Python compile error
  • Added guidance for proper date formatting to ensure Analysis Start Date works properly
  • Added option for user to upload a PDF (e.g. an offering or financing memorandum) from which the GPT may extract some or all inputs
  • Added standard disclaimer

v1.0

• Initial release
• Built based on v3.1 of the ‘Advanced Mortgage Amortization’ model