Need some Help with Amortization Excel Spreadsheet

[Deanna]

I am hoping to get some help with the attached file.

1. The "actual number of payments" blank a the top does not auto fill.
2. I am wanting to add a late fee of $42.50 for any payments 10 days late.
3. A double payment was made on 4/20/16 and I am wanting to make sure this computed correctly in the spreadsheet.

Can anyone help me with this?

Thank you.

 

[AlanSidman]

Perhaps a worksheet open event

Option Explicit

Private Sub Workbook_Open()
Dim lr As Long
lr = Range("M" & Rows.Count).End(xlUp).Row
lr = lr - 13
Range("K6") = lr
End Sub

 

[Deanna]

Perhaps a worksheet open event

Option Explicit

Private Sub Workbook_Open()
Dim lr As Long
lr = Range("M" & Rows.Count).End(xlUp).Row
lr = lr - 13
Range("K6") = lr
End Sub

Sorry, I don't understand your response. I am not that experienced in excel to understand your reply, but thank you anyway for responding.

 

[vletm]

1. The "actual number of payments" blank a the top does not auto fill.
> How it would 'auto fill' without any formula?
> Do You have any older version of same file and check K6-cell formula?

 

[bosco_yip]

Regarding the "actual number of payments", I think the OP wanted to fill the cell with formula.

1] Amend formula in K5 (Scheduled Number of Payments):

=IF(F4*F5*F6*F8>0,COUNTIF(E13:E1000,">0"),"")

2] Enter formula in K6 (Actual Number of Payments):

=IF(F4*F5*F6*F8>0,COUNTIF(INDIRECT("C13:C"&K5),">0"),"")

3] Please see attachment.

Regards
Bosco

 

[David Evans]

I've amended your file somewhat - I've made your payments made an Excel Table and used it to drive most of the questions you posed. Some of the questions you posed are open to a different interpretation, so if I have mis-understood, please advise and clarify.

I added a Late Fee and an Early Payment column calculated on the payment date-payment due date with the appropriate constants.

You insert a row beneath the final row to add more payments as and when you make them.

Hope it helps