Loan calculation formula

JaJackso2024  wrote:  ``to be paid back by the estate after their passing``

Since it sounds like there will be no payments in their lifetime, you don't need complex amortization schedules, IMHO.

The following paradigm might meet your needs.  See the attached Excel file for details.

For this example, I choose random dates, loan amounts and annual interest rates.  Of course, you would replace all that with actual data.

Also for this example, I use =TODAY() for the Valuation Date.  That might be useful so that you can know the current total value due at any time.  But you would replace that with a date of death eventually.

Before you can proceed with the calculations in columns D, E and F, you need to make some decisions.

1. Will you charge simple interest or compound interest?

2. If compound interest, what is the compounding frequency?

3. And if compound interest, how is the sub-annual interest rate calculated based on the annual rate?

I assume compound monthly interest.  And I assume that the monthly rate is annual/12.

I also assume a simple daily rate of annual/365 for the last partial month of the loan duration.

Thus, the total interest calculation in F5 is:

ROUND(FV(C5/12, D5, 0, -B5) * (1 + E5*C5/365) - B5, 2)

And the total balance due calculation in G5 is:

B5 + F5

The number of months for the current loan duration in D5 is:

DATEDIF(A5, $A$1, "m")

And the number of current remaining days for the last partial month in E5 is:

MAX(0, $A$1 - EDATE(A5, D5))

I have learned not to trust DATEDIF(..., "md").  And MAX(0, ...) should be superfluous ("defensive  programming").

Finally, the overall total in G4 is calculated by:

SUM(G5:INDEX(G:G, LOOKUP(1E+300, G:G)))

LOOKUP(1E+300, ...) returns the row number of the last numeric value in column G.