Loan Balance given interest rate and non-fully amortized payments

[Billt4]

Suppose I take out a $100,000 loan with (say) 6% interest rate, but do not pay the fully amortized payment each month. After (say) 10 payments, I want to know the remaining loan balance.

How do I do this?

Thanks,

Bill

 

[Dave Patton]

Your information is not complete.
When did you acquire the loan?
How is the interest calculated? Compounding etc.
How is the interest calculated on late payments?
What payments did you make and on what dates?
For an example, I assumed payments made on 1st of month.
You can prepare a schedule with the relevant information.

T202008c.xlsm
	A	B	C	D	E
1	Loan	$100,000.00
2	Interest	6%
3
4			Payment	Interest	Balance
5	1		0.00	500.00	100,500.00
6	2		100.00	502.00	100,902.00
7	3		0.00	504.51	101,406.51
8	4		0.00	507.03	101,913.54
9	5		0.00	509.57	102,423.11
10	6		0.00	512.12	102,935.23
11	7		0.00	514.68	103,449.90
12	8		0.00	517.25	103,967.15
13	9		0.00	519.84	104,486.99
14	10		2,000.00	512.43	102,999.42
15
2d

Cell Formulas
Range	Formula
D5	D5	=B1*$B$2/12
E5	E5	=B1+D5-C5
D6:D14	D6	=(E5-C6)*$B$2/12
E6:E14	E6	=E5-C6+D6
A6:A14	A6	=A5+1

 

[Billt4]

Thank you. I was hoping there was a formula (similar to PMT) that would do this.

Preparing a schedule will be a bit complex since actually I am looking at ten years, but yeah, do-able if there is no formula.
Yes, interest is compounded. It's a home loan, I just gave $100K as an easy example to look at.

Dates are TBD, as I haven't gotten the loan yet.

Thanks, let me know if there is a formula for outstanding loan balance.

 

[Dave Patton]

We can provide relevant formulas if you provide complete information.

You can check Excel's functions; check the category "Financial".

A quick example

T202008c.xlsm
	A	B	C	D	E
1	Loan	$100,000.00
2	Interest	6%
3	Term	25
4	Payment	$644.30
5
6
7			Payment	Interest	Balance
8	1		644.30	500.00	99,855.70
9	2		644.30	496.06	99,707.45
10	3		644.30	495.32	99,558.47
11	4		644.30	494.57	99,408.74
12	5		644.30	493.82	99,258.26
13	6		644.30	493.07	99,107.03
14	7		644.30	492.31	98,955.04
15	8		644.30	491.55	98,802.29
16	9		644.30	490.79	98,648.78
17	10		644.30	490.02	98,494.50
2d

Cell Formulas
Range	Formula
B4	B4	=PMT(B2/12,B3*12,-B1,0)
C8:C17	C8	=$B$4
D8	D8	=B1*$B$2/12
E8	E8	=B1+D8-C8
D9:D17	D9	=(E8-C9)*$B$2/12
E9:E17	E9	=E8-C9+D9
A9:A17	A9	=A8+1

 

[Billt4]

Yes, thank you - that much I know how to do.

Thanks!

- Bill