Forum Discussion
Promissory note spreadsheet with accrued int calc and then principal and interest pmts
Here is an example of how you can create an amortization schedule in Excel for your promissory note with accrued interest followed by principal and interest payments.
I'll use the example data you provided:
- Loan amount: $2,000,000
- Annual interest rate: 12%
- Disbursements:
- $500,000 on 10/13/2023
- $1,500,000 on 10/17/2023
We'll create a schedule that includes accrued interest payments from November 2023 to March 2024 and principal and interest payments from April 2024 to October 2025. The entire sum due will be paid on or before October 12, 2025.
- Create a Data Table
In one worksheet, create a data table like this:
Date | Payment Type | Payment Amount | Remaining Balance |
10/13/2023 | Disbursement | $500,000 | $1,500,000 |
10/17/2023 | Disbursement | $1,500,000 | $0 |
11/01/2023 | Accrued Int | Formula 1 | Formula 2 |
12/01/2023 | Accrued Int | Formula 1 | Formula 2 |
01/01/2024 | Accrued Int | Formula 1 | Formula 2 |
... | ... | ... | ... |
- Calculating Accrued Interest
In cell D3 (Payment Amount for 11/01/2023), you can use the following formula to calculate the interest for the month:
=IF(AND(B3="Accrued Int", C3 <> ""), C3*(12%/12), "")
This formula checks if the payment type is "Accrued Int" and if there's a payment amount in the previous column (C3). If both conditions are met, it calculates the interest for that month based on the annual rate of 12%.
- Calculating Remaining Balance
In cell E3 (Remaining Balance for 11/01/2023), you can use the following formula to calculate the remaining balance:
=IF(B3="Accrued Int", E2-D3, "")
This formula checks if the payment type is "Accrued Int" and subtracts the accrued interest payment from the previous balance. The balance is carried forward to the next month.
- Principal and Interest Payments
You can continue this pattern for subsequent months. For example, in cell A5, enter "04/01/2024" for the date, "P&I Payment" for the payment type, and "=$100,000" for the payment amount. In cell E5, calculate the remaining balance as follows:
=IF(B5="P&I Payment", E4-D5, "")
- Final Balloon Payment
Calculate the final balloon payment due on or before October 12, 2025. You'll add the remaining principal balance and any accrued interest as of that date.
Date | Payment Type | Payment Amount | Remaining Balance |
... | ... | ... | ... |
10/12/2025 | Balloon Pay | Formula 3 | 0 |
In cell D23 (Payment Amount for 10/12/2025), you can calculate the final balloon payment:
=IF(B23="Balloon Pay", E22+D22, "")
This formula checks if the payment type is "Balloon Pay" and adds the remaining principal balance (E22) and any accrued interest (D22) to calculate the final payment.
This should give you a basic structure for your amortization schedule with accrued interest and principal and interest payments. You can then fill in the table for each month between the dates you have.
The text was created with the help of AI.
Additional I recommend always to inform about your Excel version, operating system, storage medium/hard drive, OneDrive, Sharepoint, etc.).
If possible, add a file (without sensitive data) and use this file to describe your project step by step, or add photos with the appropriate description.
In this link you will find some more information about it:
Welcome to your Excel discussion space!
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.