Loan Amortization Issues

[thoyler]

I need to produce a loan amortization with the ability to input actual historic payments made or missed (need to add a late fee to missed or late payments) as well as different loan start date & beginning payment date. I have searched office templates, but only found a general Mortgage calculator. I have no idea what formulas I would need to even produce such a template.
PLEASE HELP!! Can somebody direct me to a site that may offer free software that performs the functions outlined above?

[alansidman]

In Excel, if you select Open-->New--> then in the address box at the top, type Loan Amortization, Excel will search the internet and provide some sample templates. You might be able to find one that is close and you can then modify.

[thoyler]

Doesn’t seem to be much out there for tracking loan payments with various late payments.
Guess I'll have to create one. Not an expert in excel, had advanced training some years ago, but this is a bit beyond me.

Thanks

[alansidman]

Mock up a spreadsheet manually as to what you would like it to look with an example. Post it here and ask for help to add formulas or VBA to make it work as you intend.

[thoyler]

I have not used Excel much for over 10 years, so I am not sure of my use of formulas. Please critique these file as needed.

Thank you for any help that may be offered.
thoyler

I have mocked up two examples (Promissory Note [Strait Amortized Schedule], Promissory Note 07_08_17 [Amortized Schedule with Late Payments & Late Fees - beginning at 04/01/2011 payment]. Both files have a 1 month delay on interest, and no Attorney Fees added; only late fees recorded.

FYI, the Terms of the Promissory Note are:

1. Date of Contract: August 18, 2003
2. Principal amount: $67,500.00
3. Annual Interest Rate: Seven Present (7.0%)
4. Maturity Date: November 1, 2018
5. Monthly Installments: $606.00 (beginning November 1, 2003)
6. Late Fee: $50.00 (if more than 5 days late, at Lender Option)
7. Reasonable Attorney Fees & Court Costs:
Attorney Rate (approximately $250.00 per hour) * these expenses will bear interest at the Annual Interest Rate from date of advance.
8. Interest on the debt evidenced by this note will not exceed the maximum rate or amount of non-usurious interest that may be contracted for, taken, received under law. Any interest in excess of maximum amount will be credited on the Principal Amount….. (I believe the max. is: 18%, including late fees)

Thanks again.

[thoyler]

P.S. Sorry I took so long to get back; I've been out of town on Business.

thoyler

[thoyler]

Oops, corrected errors in files (07/10/17)

thoyler

[thoyler]

Bump no response

[alansidman]

Been travelling myself. Will look at this in the next day or so, once I get home life back to normal.

[alansidman]

Appear reasonable. Do they give you the expected results. If yes, then I think you are on target.

[thoyler]

I have revised the File (changed layout & some formulas). See attachment.
1) Need to limit the periods (months) to 30 days each in column "N (rows 12-192)".
2) Need to calculate only if column "N(row number)" is a positive number (>0), in column "G (rows 12-192)".
3) Need to calculate only if column "N(row number)" is a negative number (<0), in column "H (rows 12-192)".


Thanks
thoyler

[alansidman]

In N12, =IF(C12-A12>29,30,C12-A12) and copy down.
In G12, =if(n12>0,D12*(1+($G$6*(N12)))-D12,0) and copy down
in H12, =IF(N12<0,D12*(1+($G$7*(N12)))-D12,0) and copy down

[thoyler]

Thanks, that worked great for columns G & H.
I'm sorry I must have been unclear about column N.
1) Column N should be limited to 30 day for each month added. For instance in row "152" it should add up to 142 rather than 145 (limiting July, August, & October to 30 days each).
Is that possible?

Thanks
thoyler

[alansidman]

See if this formula works for you.

=DATEDIF(A152,C152,"M")*30+(DATEDIF(EOMONTH(C152,-1),C152,"D")-1)

[thoyler]

It returns the Date: "5/21/1900"

[shg]

If you format that as Number instead of a date, that's 142.

[thoyler]

OK, that works, except for instances where they payed early; it returns "#NUM!" (for negative numbers).

[alansidman]

Using line 173 as an example. =IF(A173>C173,0,DATEDIF(A173,C173,"M")*30+(DATEDIF(EOMONTH(C173,-1),C173,"D")-1))

copy this line to all in the column. It seems that all your issues surround the IF-Then situation. Perhaps a short tutorial on IF-Then

Look at these links
https://www.techonthenet.com/excel/formulas/if.php
https://www.techonthenet.com/excel/macros/if_custom.php

[thoyler]

This formula still returns zero for all negative numbers. I'll read the material you’ve suggested.

I am also trying to work out a limiting function for interest. Tried different things all day yesterday??
* Interest needs to be limited to a maximum of 18% per year. This is a usury limit standard in most states.

Thank You very much for your help.
thoyler

[alansidman]

This formula still returns zero for all negative numbers.

That is because, I assumed if they paid early then there would be no penalty. If they pay what result did you expect to see.

[thoyler]

I'm sorry if I was unclear. I'm not use to formulas in Excel, and I'm sure I am unorthodox in my methods.
There would be a credit; represented by the negative number.
They paid early in some cases; the interest would stop that (negative) number of days early.

In this case:

Positive = late
Negative = early

and no period (month) can be greater than 30 days. Its a 360 day year/12 (financial year).

Thanks
thoyler

[ojbego]

How can I get a copy of the spreadsheet?

[FDibbins]

How can I get a copy of the spreadsheet?

It appears that (unfortunately) the file has been removed