Formula to calculate outstanding loan balance

beejays

New Member
Joined
Aug 30, 2018
Messages
4
I have a spreadsheet with a whole lot of different finance contract details and I would like a formula which auto calculates the outstanding loan balance as at today, or even just at the beginning of the current month, whichever is easier.

The information in the spreadsheet is;
-loan repayment (fixed amount)
-loan term
-balloon amount (if applicable)
-original loan amount


What is the best way of achieving this?
 

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.
More of a finance question I guess. If you have a rate it is easier...

Here is a simple calculation to calculate a loan value based on the # of months. You first have to find the rate and then calculate the number of months remaining, to get the present value (or loan value). This is a simple illustration and I did not include a balloon payment.

The Formula is in COL D. The ROWS run normal.


COL BCOL CCOL DCOL E
Monthly Loan Payment$1,000
Loan Term (Years)5
Original Loan Amount $25,000.00
Rate=RATE(C3*12,C2,-C4,0)<- Find The Rate, As It was Not Given,
Turn Years into Months, to Match Payment
Current Date
Loan Start Date
Number of Months=DATEDIF(C10,C9,"m")< - Find Number of Months Left
Original Months=C3*12
Number of Months Left=C12-C11
Current Loan Value=-PV(C5,C13,C2,0)< - Calculate Present Value of Cash Flow Stream

<tbody>
[TD="align: right"]3.49%[/TD]

[TD="align: right"]8/30/2018[/TD]

[TD="align: right"]8/30/2017[/TD]

[TD="align: right"]12[/TD]

[TD="align: right"]60[/TD]

[TD="align: right"]48[/TD]

[TD="align: right"]$23,135.84[/TD]

</tbody>
 
Upvote 0
Thanks teksp0rt. I was trying to avoid finding interest rates for 50+ finance contracts if possible.. or should I be able to use the RATE formula to work this out?

If I have to find the interest rates I will, I was thinking something along the lines of the PV formula in conjunction with something that takes todays date into account.

An example is:
Loan amount $268,400
Balloon payment $45,454.54
Repayment amount $4,645.94 (59 payments with the 60th payment being the balloon)
 
Upvote 0
Thanks teksp0rt. I was trying to avoid finding interest rates for 50+ finance contracts if possible.. or should I be able to use the RATE formula to work this out?

If I have to find the interest rates I will, I was thinking something along the lines of the PV formula in conjunction with something that takes todays date into account.

An example is:
Loan amount $268,400
Balloon payment $45,454.54
Repayment amount $4,645.94 (59 payments with the 60th payment being the balloon)


You can just use the rate formula. It will not be perfect but will be very close, maybe off by a few hundredths... guessing. Make sure that you convert the payments if you are going to use years, seems like you get it.

For a ballon formula, I think you would just add it as your FV in your PV formula. Let me know if you want an example.
 
Upvote 0
You can just use the rate formula. It will not be perfect but will be very close, maybe off by a few hundredths... guessing. Make sure that you convert the payments if you are going to use years, seems like you get it.

For a ballon formula, I think you would just add it as your FV in your PV formula. Let me know if you want an example.

So once I have the rate, what formula would you use to have a automatically reducing balance that takes into account today's date? Sorry...I'm an excel novice at best!
 
Upvote 0
you need to know how many payments are left on the loan. so you will also need to know the date the loan began.

I would tackle it like this. First, use the rate formula, to find your rate....

=RATE(number of payments cell, payment amount cell, (-) original loan value cell, balloon payment cell) x 12

note that original loan value is negative and you multiple by 12 to make it annual.

then...

find the number of payments remaining. take the original loan date, less todays date.

you can use the function =number of payments cell (e.g. 59) - DATEDIF(original loan date, today(), "m")

...the second part of this function will calculate the number of months.


Next, calculate the current loan value by...

= -PV(rate cell / 12, number of payments remaining cell, payment cell, balloon payment cell)

and you can check the math here:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}</style>https://www.mycalculators.com/ca/loancalculatorballoonm.html
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

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
Back
Top