Forum Discussion
Solved
Loan calculation formula
At various points during the year I will be loaning my parents lump sums of money (about $10,000 to $20,000 at a time), to be paid back by the estate after their passing. We have agreed on 5% interest, although it might be good to set up the document with the interest rate as an assumption that could be changed later. I'd like to set up an excel document to keep track of the amounts I loan to them, and the interest added to that loan. I'm basically acting as a home equity line of credit; loaning money when needed, to be paid back at a future date, with interest. Any ideas on a formula to be used?
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.
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.
- This is very helpful. Thank you! We will consider your excellent questions and move forward. Much appreciated.
Not sure this will do exactly what you expect but try the 'Loan Amortization Schedule' template
File > New, then at the bottom of the window search for Loan Amortization Schedule in Office:
Thank you for the help. Much appreciated.Lorenzo
JaJackso2024 You're welcome & thanks for providing feedback
