# Loan Balance given interest rate and non-fully amortized payments

#### Billt4

##### New Member
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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Dave Patton

##### Well-known Member
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
ABCDE
1Loan\$100,000.00
2Interest6%
3
4PaymentInterestBalance
510.00500.00100,500.00
62100.00502.00100,902.00
730.00504.51101,406.51
840.00507.03101,913.54
950.00509.57102,423.11
1060.00512.12102,935.23
1170.00514.68103,449.90
1280.00517.25103,967.15
1390.00519.84104,486.99
14102,000.00512.43102,999.42
15
2d
Cell Formulas
RangeFormula
D5D5=B1*\$B\$2/12
E5E5=B1+D5-C5
D6:D14D6=(E5-C6)*\$B\$2/12
E6:E14E6=E5-C6+D6
A6:A14A6=A5+1

#### Billt4

##### New Member
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

##### Well-known Member
We can provide relevant formulas if you provide complete information.

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

A quick example

Cell Formulas
RangeFormula
B4B4=PMT(B2/12,B3*12,-B1,0)
C8:C17C8=\$B\$4
D8D8=B1*\$B\$2/12
E8E8=B1+D8-C8
D9:D17D9=(E8-C9)*\$B\$2/12
E9:E17E9=E8-C9+D9
A9:A17A9=A8+1

#### Billt4

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

Thanks!

- Bill

Replies
8
Views
218
Replies
5
Views
176
Replies
1
Views
140
Replies
1
Views
247
Replies
11
Views
263

1,129,795
Messages
5,638,379
Members
417,025
Latest member
MusterDuster

### 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?

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