Forum Discussion
Promissory note spreadsheet with accrued int calc and then principal and interest pmts
I need an amortization schedule for the following promissory note that has varying "types" of payments...first, accrued interest only, and then principal and interest later until the end of the note:
FOR VALUE RECEIVED the undersigned, Borrower, an Illinois corporation ("Payor") promises to pay to the order of Lender, a Michigan limited liability company ("Payee"), the principal amount of $2,000,000 [see far below disbursement dates], plus interest on the unpaid principal at the rate of 12%, compounded annually.
Payment Schedule and Amounts:
Accrued interest only on this Note shall be paid monthly on or before the first day of each month starting November 1, 2023, through March 31, 2024. Commencing on the 1st day of April, 2024 and on the first of each month thereafter, principal and interest shall be payable in installments of $100,000 per month. Notwithstanding the above, the entire sum due, principal and interest, shall be paid on or before October 12, 2025.
Disbursements made:
10/13/2023 $500,000
10/17/2023 $1,500,000
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.
- Hi,
Dumb question, but are you able to create the spreadsheet with the first 4-5 rows with the columns and cell formulas and attach?
