Please help

[amyr]

I did an owner carry loan and the borrower has had a very sketchy start with payments. The 30 year loan was for $313,600.00 at 9% and payments are $2,523.00. he missed the first payment then had a few partial payments. I need to figure out how to calculate the amount due, with interest and late fees. He is only 8 months into the loan so far.

 

[Cubist]

You would need to provide the schedule of his late payments and the amount of each.

 

[amyr]

Late fees are 5% of any payment u

You would need to provide the schedule of his late payments and the amount of each.

 

[amyr]

I agreed to waive late fees on first six months
Month 1: 0.00
Month 2: paid in full
Month 3: $300 unpaid
Month 4 1000 unpaid
Month 5: 1000 unpaid
Month 6: 1100 unpaid
Month 7 1200 overpaid on time
Month 8 paid in full but late
Month 9 paid in full but late
Month 10 500 overpaid on time

 

[amyr]

Late fees are 5% of any payment unpaid

 

[Cubist]

Late fees are 5% annually compounded monthly i.e. 5%/12 per month or 5% per month?

 

[Cubist]

Book4
	A	B	C	D	E	F	G	H	I	J	K
1	Loan Amount	$ 313,600
2	Interest	9%
3	PMT	$2,523.30
4	Late fee rate	5%
5
6
7	Month	Beg Balance	Interest	Scheduled Payment	Actual Payment	Unpaid	Paid On Time	Late fee	Actual End Balance	Scheduled Ending Balance	Catch up amount
8	0	$ 313,600	$ -	$ -	$ -	$ -	Waived	$ -	$ 313,600	$ 313,600	$ -
9	1	$ 313,600	$ 2,352	$ 2,523	$ -	$ 2,523	Waived	$ -	$ 315,952	$ 313,429	$ 2,523
10	2	$ 315,952	$ 2,370	$ 2,523	$ 2,523	$ -	Waived	$ -	$ 315,798	$ 313,256	$ 2,542
11	3	$ 315,798	$ 2,368	$ 2,523	$ 2,223	$ 300	Waived	$ -	$ 315,944	$ 313,082	$ 2,861
12	4	$ 315,944	$ 2,370	$ 2,523	$ 1,523	$ 1,000	Waived	$ -	$ 316,790	$ 312,907	$ 3,883
13	5	$ 316,790	$ 2,376	$ 2,523	$ 1,523	$ 1,000	Waived	$ -	$ 317,642	$ 312,731	$ 4,912
14	6	$ 317,642	$ 2,382	$ 2,523	$ 1,423	$ 1,100	Waived	$ -	$ 318,601	$ 312,553	$ 6,049
15	7	$ 318,601	$ 2,390	$ 2,523	$ 3,723	$ -	On-time	$ -	$ 317,268	$ 312,374	$ 4,894
16	8	$ 317,268	$ 2,380	$ 2,523	$ 2,523	$ -	Late	$ 126	$ 317,250	$ 312,193	$ 5,057
17	9	$ 317,250	$ 2,379	$ 2,523	$ 2,523	$ -	Late	$ 126	$ 317,232	$ 312,011	$ 5,221
18	10	$ 317,232	$ 2,379	$ 2,523	$ 3,023	$ -	On-time	$ -	$ 316,588	$ 311,828	$ 4,760
19	11	$ 316,588	$ 2,374	$ 2,523	$ 7,319.19	$ -	On-time	$ -	$ 311,643	$ 311,643	$ -
Sheet2

Cell Formulas
Range	Formula
B3	B3	=PMT(B2/12,30*12,-B1,0,0)
A8:A368	A8	=SEQUENCE(30*12+1,1,0)
B8	B8	=$B$1
H8:H19	H8	=IF(G8="Late",D8*$B$4,0)
I8:I19	I8	=B8+C8+H8-E8
B9:B19	B9	=I8
C9:C19	C9	=B9*$B$2/12
D9:D19	D9	=$B$3
F8:F19	F8	=IF(D8-E8<0,0,D8-E8)
E10,E16:E17	E10	=D10
E11	E11	=D11-300
E12:E13	E12	=D12-1000
E14	E14	=D14-1100
E15	E15	=D15+1200
E18	E18	=D18+500
E19	E19	=K18*(1+$B$2/12)+D19
K8:K19	K8	=I8-J8
Dynamic array formulas.

I am assuming a 5% late fee for each scheduled payment i.e. 5%($2,523) = $126.16 for each late month.
If the late fees are not paid, they get added to the loan balance and compounded at 9% monthly.

Based on these assumptions, this brings the current catch-up amount to $4,760 due immediately.
If this amount is paid at month 11, it should get compounded for interest i.e. $4,760*(1+9%/12) = $4,705.89
With the regular scheduled payment and the catch-up amount, the total due for month 11 is $4,705.89 + $2,523 = $7,319.19.

 

[Cubist]

There's a typo above.

I am assuming a 5% late fee for each scheduled payment i.e. 5%($2,523) = $126.16 for each late month.
If the late fees are not paid, they get added to the loan balance and compounded at 9% monthly.

Based on these assumptions, this brings the current catch-up amount to $4,760 due immediately.
If this amount is paid at month 11, it should get compounded for interest i.e. $4,760*(1+9%/12) = $4,705.89 $4,795.89
With the regular scheduled payment and the catch-up amount, the total due for month 11 is $4,795.89 + $2,523 = $7,319.19.

 

[amyr]

Omg thank you a billion times over! This is exactly what I needed!!

 

[amyr]

There's a typo above.

I am assuming a 5% late fee for each scheduled payment i.e. 5%($2,523) = $126.16 for each late month.
If the late fees are not paid, they get added to the loan balance and compounded at 9% monthly.

Based on these assumptions, this brings the current catch-up amount to $4,760 due immediately.
If this amount is paid at month 11, it should get compounded for interest i.e. $4,760*(1+9%/12) = $4,705.89 $4,795.89
With the regular scheduled payment and the catch-up amount, the total due for month 11 is $4,795.89 + $2,523 = $7,319.19.