Personal Loan XLS with Periodic Payments

deilert

New Member
Joined
Jul 20, 2007
Messages
9
I'm curious if anyone has seen a spreadsheet for a personal loan?

All I've been able to find is car and home loans which have a fixed period for the loan and all have regular payments. The payee is not making regular payments so I'm computing interest daily. When a payment comes in, that's taken off the balance and we just continue charging interest each day on the new amount.

I'd like to show the current balance, any payments, the principle amount, the interested charged, and daily charge on the balance.

I'm hoping that this will allow them to see how fast the interest is racking up, and how there payments do make a difference.

Any help would be awesome!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
Welcome to the Board.

Are you charging interest for weekends since this is not a regular loan?

If so in A2 put the beginning date.
Then drag the date down column A as far as needed.
In B2 put the starting value of the loan.
Then in B3 put the formula "=D2" then copy this formula down
In C2 put formula: =B2*annualinterestrate/365. This would be the interest charged that day on the loan.
In D2 put "=C2" then in D3 put "=C3+D2" and copy all the way down. This will keep an accumulated total of the interest
In E2 put a payment in if applicable.
In F2 put formual =B2+C2-E2. This would be the ending balance at the end of a day.

You could also just put payment dates and then you could calculate the number of days between payments and calculate interest on the time between then. One other thing, would you be charging interest for the day before or after a payment was made that day? Right now it charges interest before a payment that day. You can modify the formula a little to charge interest after the payment that day. Let me know if this is sort of what you are looking for?
 

deilert

New Member
Joined
Jul 20, 2007
Messages
9
Hey Thanks,

The simplicity of it was great. I was making it too hard in my head. I've added a couple of additional items for this specific loan (Feb 06 interest was only 15%). The sheet continues down and March starts up at 20% (I know it's high, they said it would be paid and signed the agreement) Oh well.

Thanks again, and here's the sheet for anyone else stuck with this problem:
Note 072007.xls
ABCDEFG
3NoteAmount$15,000.00
4OriginationDate10/19/05
5DueDate02/01/06
6Points$1,500.00
7LateInterest20.00%
8Feb'06Interest15.00%
9
10DatePrincipleDailyIntPaymentBalancePointsTotalDue
112/1/2006$15,000.00$6.16$15,006.16$1,500.00$16,506.16
122/2/2006$15,006.16$6.17$15,012.33$1,500.00$16,512.33
132/3/2006$15,012.33$6.17$2,000.00$13,018.50$1,500.00$14,518.50
142/4/2006$13,018.50$5.35$13,023.85$1,500.00$14,523.85
152/5/2006$13,023.85$5.35$13,029.20$1,500.00$14,529.20
Sheet1
 

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,694
Latest member
dpatete

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