How do I use Excel 2007 to determine late payment amounts
 

I was told a spreadsheet program could figure this for me easily. I know the
basics of Excel but I'm not an expert. Here's the situation.

I sold land to Sam on contract for $35,000 with no interest. The terms of
the contract require payments of $500 per month due on or before the first of
the month until the loan is paid off. If a payment is more than 5 days late,
10% yearly interest is charged until the payment is paid in full. Sam has
yet to make a full payment.

How can Excel 2007 be used to track the actual amount (payment plus
interest) Sam owes? I want to track this by monthly payment and total amount
due to date. I want the application to calculate the number of days late per
payment, the interest due per payment to date, the total interest due to
date, and the total amount due to date.

Thanks!! pecc19

How do I use Excel 2007 to determine late payment amounts
 

I've uploaded a workbook (Excel 2007 .xlsx format) that should help you out
on this. You can examine the formulas in it to see how it was done. Since
http uploads seem to have a problem with 2007 filenames (at least I am having
problems), I've put the Excel file into a .zip file. Click the link below
and save to your hard drive, then open that file and extract the .xlsx file
that is in it.

On the 'Easy...' sheet you can simply enter the actual information (Sam's
real name, date you made the loan, and date 1st pmt was due) and rest should
update automatically. Then you can enter actual payments/payment dates in
place of the few sample entries I made. Since you said it was late if more
than 5 days late and is due on 1st of month, formula to determine if interest
accrued will calculate the interest accrued if payment is made on or after
7th of the month or if the payment amount is less than agreed on each month
($500).

If you need a tool to unzip it, try WinZip or WinRAR. WinZip demo available
free at www.winzip.com
http://www.jlathamsite.com/uploads/for_pecc19.zip

"pecc19" wrote:

I was told a spreadsheet program could figure this for me easily. I know the
basics of Excel but I'm not an expert. Here's the situation.

I sold land to Sam on contract for $35,000 with no interest. The terms of
the contract require payments of $500 per month due on or before the first of
the month until the loan is paid off. If a payment is more than 5 days late,
10% yearly interest is charged until the payment is paid in full. Sam has
yet to make a full payment.

How can Excel 2007 be used to track the actual amount (payment plus
interest) Sam owes? I want to track this by monthly payment and total amount
due to date. I want the application to calculate the number of days late per
payment, the interest due per payment to date, the total interest due to
date, and the total amount due to date.

Thanks!! pecc19

How do I use Excel 2007 to determine late payment amounts
 

RE XL2007 files on web: I have found that if a website has a file called
ABC.XLSX and I download it, then it becomes ABC.ZIP (Office 2007 files are
really a collection of files in a zip wrapper) All I need to is rename the
file giving it the correct extension and it opens just fine in XL2007
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
I've uploaded a workbook (Excel 2007 .xlsx format) that should help you
out
on this. You can examine the formulas in it to see how it was done.
Since
http uploads seem to have a problem with 2007 filenames (at least I am
having
problems), I've put the Excel file into a .zip file. Click the link below
and save to your hard drive, then open that file and extract the .xlsx
file
that is in it.

On the 'Easy...' sheet you can simply enter the actual information (Sam's
real name, date you made the loan, and date 1st pmt was due) and rest
should
update automatically. Then you can enter actual payments/payment dates in
place of the few sample entries I made. Since you said it was late if
more
than 5 days late and is due on 1st of month, formula to determine if
interest
accrued will calculate the interest accrued if payment is made on or after
7th of the month or if the payment amount is less than agreed on each
month
($500).

If you need a tool to unzip it, try WinZip or WinRAR. WinZip demo
available
free at www.winzip.com
http://www.jlathamsite.com/uploads/for_pecc19.zip

"pecc19" wrote:

I was told a spreadsheet program could figure this for me easily. I know
the
basics of Excel but I'm not an expert. Here's the situation.

I sold land to Sam on contract for $35,000 with no interest. The terms
of
the contract require payments of $500 per month due on or before the
first of
the month until the loan is paid off. If a payment is more than 5 days
late,
10% yearly interest is charged until the payment is paid in full. Sam
has
yet to make a full payment.

How can Excel 2007 be used to track the actual amount (payment plus
interest) Sam owes? I want to track this by monthly payment and total
amount
due to date. I want the application to calculate the number of days late
per
payment, the interest due per payment to date, the total interest due to
date, and the total amount due to date.

Thanks!! pecc19

How do I use Excel 2007 to determine late payment amounts
 

I think it may have to do with what services and abilities are on the HTTP
server. When I put a link directly to the file (with .xlsx extension) into
IE 7, my site responded with a file not found message. Then when I just
changed the extension to .xls with the intent of telling user to change it to
..xlsx once downloaded, the darned thing opened up in IE showing the raw
content of the file - not even as a spreadsheet. That's when I zipped it up
and put it out there.

I think we're going to see some troubles (at least I am) in moving Office
2007 files around on the internet with anything other than FTP for a while.
Seems to even be a problem moving via HTTP from "here to there": I tried to
submit an Excel 2007 template to Microsoft, couldn't get the job done, no
matter what I did the MSFT site wouldn't accept the file. At the time I
thought it was because the file had some VBA code in it, but now I don't know.

"Bernard Liengme" wrote:

RE XL2007 files on web: I have found that if a website has a file called
ABC.XLSX and I download it, then it becomes ABC.ZIP (Office 2007 files are
really a collection of files in a zip wrapper) All I need to is rename the
file giving it the correct extension and it opens just fine in XL2007
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
I've uploaded a workbook (Excel 2007 .xlsx format) that should help you
out
on this. You can examine the formulas in it to see how it was done.
Since
http uploads seem to have a problem with 2007 filenames (at least I am
having
problems), I've put the Excel file into a .zip file. Click the link below
and save to your hard drive, then open that file and extract the .xlsx
file
that is in it.

On the 'Easy...' sheet you can simply enter the actual information (Sam's
real name, date you made the loan, and date 1st pmt was due) and rest
should
update automatically. Then you can enter actual payments/payment dates in
place of the few sample entries I made. Since you said it was late if
more
than 5 days late and is due on 1st of month, formula to determine if
interest
accrued will calculate the interest accrued if payment is made on or after
7th of the month or if the payment amount is less than agreed on each
month
($500).

If you need a tool to unzip it, try WinZip or WinRAR. WinZip demo
available
free at www.winzip.com
http://www.jlathamsite.com/uploads/for_pecc19.zip

"pecc19" wrote:

I was told a spreadsheet program could figure this for me easily. I know
the
basics of Excel but I'm not an expert. Here's the situation.

I sold land to Sam on contract for $35,000 with no interest. The terms
of
the contract require payments of $500 per month due on or before the
first of
the month until the loan is paid off. If a payment is more than 5 days
late,
10% yearly interest is charged until the payment is paid in full. Sam
has
yet to make a full payment.

How can Excel 2007 be used to track the actual amount (payment plus
interest) Sam owes? I want to track this by monthly payment and total
amount
due to date. I want the application to calculate the number of days late
per
payment, the interest due per payment to date, the total interest due to
date, and the total amount due to date.

Thanks!! pecc19