Personal Loan XLS with Periodic Payments

[deilert]

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!

 

[schielrn]

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]

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
	A	B	C	D	E	F	G
3	NoteAmount	$15,000.00
4	OriginationDate	10/19/05
5	DueDate	02/01/06
6	Points	$1,500.00
7	LateInterest	20.00%
8	Feb'06Interest	15.00%
9
10	Date	Principle	DailyInt	Payment	Balance	Points	TotalDue
11	2/1/2006	$15,000.00	$6.16		$15,006.16	$1,500.00	$16,506.16
12	2/2/2006	$15,006.16	$6.17		$15,012.33	$1,500.00	$16,512.33
13	2/3/2006	$15,012.33	$6.17	$2,000.00	$13,018.50	$1,500.00	$14,518.50
14	2/4/2006	$13,018.50	$5.35		$13,023.85	$1,500.00	$14,523.85
15	2/5/2006	$13,023.85	$5.35		$13,029.20	$1,500.00	$14,529.20
Sheet1