Irregular loan repayment schedule

jjspi

New Member
Joined
Jan 29, 2005
Messages
2
I took out a loan from my parents for $25,000 on Nov 1, 2003 and they are charging me 6% simple interest yearly. I was suppose to pay them back every month $268 for the next 10 years. However, I didn't really keep with the payments and made a few payments on different months and even 1 of the months I made a payment of about $1,000. I am looking to just pay off the loan this month but need to figure out how to calculate how much I actually still owe them.

Nov 1, 2003
Loan $25,000
6% annual interest
Sporadic payments of differents dollar amounts
looking to pay off loan in full Feb. 1, 2005
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
The normal way to pay off a loan, called an amortization, is to pay the a fixed amount each month, as in your case. The amount consists of two parts, the interest on the outstanding loan since the last payment (one month's interest), plus a certain amount that reduces the outstanding debt.

In your case, you borrowed $25,000 on N0v 1, 2003, and you are to pay them $268 each month for the next 10 years. In a normal amortisation plan, I calculate the monthly payment as $277.56, so, I don't know how your figure of $268 was arrived at.

In any case, you will have to calculate what you owe your parents as of the current day, by figuring out that you were going to pay them $268/month, of which, for the first month, 6%*25,000/12 = $125 would have been interest, and the remaining 268-125=$143 would have been payment on your debt, leaving 25,000-143=$24,857 as the debt at the beginning of the second month. You would recalculate the interest on that debt at the end of the month, subtract the interest from the debt, and that would be the new payment on the debt, etc., etc. Any overpayment, say $1000, instead of $268, you would simply calculate the interest due for the month(s) since your last payment, subtract that interest payment from the $1000 payment, and deduct that remainder from the debt. Etc. But, you have to know each date of payment. Failing that, you will just have to guess as to the actual payment dates, check with your parents and, if they agree, go with that.

Now, all the above can easily be programmed into an Excel spreadsheet, using formulas and some common sense.

If you have a problem doing this, send me a PM with your email address and all the data that you can, and I will communicate with you in by email, so that I can then attach a spreadsheet that does this for you.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top