Loan Balance given interest rate and non-fully amortized payments

Billt4

New Member
Joined
Aug 19, 2020
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Feb 15, 2002
Messages
4,662
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Aug 19, 2020
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Feb 15, 2002
Messages
4,662
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Aug 19, 2020
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
Yes, thank you - that much I know how to do.

Thanks!

- Bill
 

Watch MrExcel Video

Forum statistics

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

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