Mortgage History Report

[asecord]

I am a newbie to excel. I would love to create a spreadsheet to track mortgage notes that I hold. I have a few clients that are not paying their notes and would like to impose a late fee. I have one that his note started back in 2008 and has only made 20 payments. What I am looking for is something to be able to enter the actual date the payment was made, the amount being paid, if 15 days late add a late fee and still track the amount of principle and interest being paid. Is this possible. Is there a formula to do this.

At wits ends!!!!

[arlu1201]

Maybe you can attach a sample workbook containing the columns ...and we can help you out.

[asecord]

[ATTACH]131286[/ATT

Please find the attachment. I hope this will be of help. Let me know if the attach didn't work or if there is anything else you may need.

[asecord]

Forgot to mention the late fees begin 15 days from due date at a percentage. I would like it if no payments have been recieved for months to be able to come up with a daily interest accrual or if only a few days late the whole late fee amount.

I can not thank you enough for taking the time to help!

Thanks

[arlu1201]

Your attachment is not visible. Can you try attaching again?

[royUK]

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[asecord]

Please let me know if this worked.

[arlu1201]

Maybe it would be easier if you put in a few values and also mention the calculations to be performed, so it would be easier to help you out.

[asecord]

What I need is something like the mortgage calculator, but I need to be able to enter the date the payment was made and the payment due date. If payment is 15 days late I would like to have it add or calculate a late fee. I found this formula on line, but I do not know how to work it in, or if it is something that will work with excel 2010.

Let say: A) charge 5% perday for all days after 15
B) start charging 8.5% interest per month on the entire amount owed including penalties after 30 days, compounded daily.
Late fee =IF(B1>15,(B1-15)*5%*A1,0)+If(B1>30,2*A1*((1+8.5%/30)^(B1-30)-1)+0.05*A1*(((1+8.5%/30)^(B1-30)-1*30/8.5%)-(B1-30)*5%*A1,0)

[arlu1201]

Is this solved? People were off for xmas so there could have been a delay in response.

[asecord]

NO!!! Not solved yet......getting very disapointed with spending all this money for a program that is very limited.