Calculation of past due Principal & Interest

jdfo · May 16th, 2002, 10:36 AM

A note was originally for $20,000, amortized over 3 year, payable monthly at 10%.The note originated 07/01/99. Nothing on this note had been paid.
The note calls for a penalty of 18% on each unpaid monthly payment until the note has been paid in full.
I need the formula to calculate the past due amounts on principal and interest payments at 18%. I have down the amortization schedule but am having trouble with the 18% due on all monthly payments past due.There are currently 34 past due months.
Thank you for your help.
John

Jay Petrulis · May 16th, 2002, 02:34 PM

Hi John,

I tried to create an amortization table with any overdue balance capitalized at the penalty interest rate, but the numbers seem a little high. Please e-mail me so I can send you a sample file, and we can go from there. I have some other ideas to test, too.

john.petrulis@notes.ntrs.com

Bye,
Jay

[ This Message was edited by: Jay Petrulis on 2002-05-16 14:38 ]

Scott R · May 16th, 2002, 04:34 PM

They should've paid $645.34 each month:
PMT(10%/12,36,20000)
Loan balance after 34 months should have been down to $1,275:
FV(10%/12,34,645.34,-20000)

They've made no payments, thus incurring an 18% penalty on the unpaid monthly payment:
18% * 645.34 = $116.16
which was added to outst note balance each month.

Loan balance after 34 months is now $31,064:
FV(10%/12,34,-116.16,-20000)

The past due amount is the current balance less originally scheduled balance or $29,789:
$31,064 - $1,275

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 16th, 2002, 10:36 AM	#1
jdfo New Member Join Date: May 2002 Location: John oakes Posts: 1	A note was originally for $20,000, amortized over 3 year, payable monthly at 10%.The note originated 07/01/99. Nothing on this note had been paid. The note calls for a penalty of 18% on each unpaid monthly payment until the note has been paid in full. I need the formula to calculate the past due amounts on principal and interest payments at 18%. I have down the amortization schedule but am having trouble with the 18% due on all monthly payments past due.There are currently 34 past due months. Thank you for your help. John

May 16th, 2002, 02:34 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi John, I tried to create an amortization table with any overdue balance capitalized at the penalty interest rate, but the numbers seem a little high. Please e-mail me so I can send you a sample file, and we can go from there. I have some other ideas to test, too. john.petrulis@notes.ntrs.com Bye, Jay [ This Message was edited by: Jay Petrulis on 2002-05-16 14:38 ]

May 16th, 2002, 04:34 PM	#3
Scott R Board Regular Join Date: Feb 2002 Location: WA state Posts: 332	They should've paid $645.34 each month: PMT(10%/12,36,20000) Loan balance after 34 months should have been down to $1,275: FV(10%/12,34,645.34,-20000) They've made no payments, thus incurring an 18% penalty on the unpaid monthly payment: 18% * 645.34 = $116.16 which was added to outst note balance each month. Loan balance after 34 months is now $31,064: FV(10%/12,34,-116.16,-20000) The past due amount is the current balance less originally scheduled balance or $29,789: $31,064 - $1,275