Formula to calculate outstanding loan balance

[beejays]

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?

 

[teksp0rt]

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 B	COL C	COL D	COL E
Monthly Loan Payment	$1,000
Loan Term (Years)	5
Original Loan Amount	$25,000.00
Rate	3.49%	=RATE(C3*12,C2,-C4,0)	<- Find The Rate, As It was Not Given,
			Turn Years into Months, to Match Payment
Current Date	8/30/2018
Loan Start Date	8/30/2017
Number of Months	12	=DATEDIF(C10,C9,"m")	< - Find Number of Months Left
Original Months	60	=C3*12
Number of Months Left	48	=C12-C11
Current Loan Value	$23,135.84	=-PV(C5,C13,C2,0)	< - Calculate Present Value of Cash Flow Stream

<tbody>
</tbody>

 

[beejays]

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)

 

[teksp0rt]

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.

 

[beejays]

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!

 

[teksp0rt]

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