How to Calculate Loan Balance at Any Given Point In Time Without Use of a Table in Excel

user_3124476 0
Viewed 13705

I'm trying to calculate the remaining balance of a home loan at any point in time for multiple home loans.

Its looks like it is not possible to find the home loan balance w/ out creating one of those long tables (example). Finding the future balance for multiple home loans would require setting up a table for ea. home (in this case, 25).

With a table, when you want to look at the balance after a certain amount of payments have been made for the home loan, you would just visually scan the table for that period...

But is there any single formula which shows the remaining loan balance by just changing the "time" variable? (# of years/mths in the future)...

An example of the information I'm trying to find is "what would be the remaining balance on a home loan with the following criteria after 10 years":

  1. original loan amt: $100K
  2. term: 30-yr
  3. rate: 5%
  4. mthly pmts: $536.82
  5. pmts per yr: 12

I'd hate to have to create 25 different amortization schedules - a lot of copy-paste-dragging...

Thanks in advance!

excel-formula excel
2 Answers

You're looking for =FV(), or "future value).

The function needs 5 inputs, as follows:

=FV(rate, nper, pmt, pv, type)

Where:

rate = interest rate for the period of interest. In this case, you are making payments and compounding interest monthly, so your interest rate would be 0.05/12 = 0.00417

nper = the number of periods elapsed. This is your 'time' variable, in this case, number of months elapsed.

pmt = the payment in each period. in your case $536.82.

pv = the 'present value', in this case the principle of the loan at the start, or -100,000. Note that for a debt example, you can use a negative value here.

type = Whether payments are made at the beginning (1) or end (0) of the period.

In your example, to calculate the principle after 10 years, you could use:

=FV(0.05/12,10*12,536.82,-100000,0)

Which produces: =81,342.32

For a loan this size, you would have $81,342.32 left to pay off after 10 years.

user_81204220

I don't like to post answer when there already exist a brilliant answer, but I want to give some views. Understanding why the formula works and why you should use FV as P.J correctly states!

They use PV in the example and you can always double-check Present Value (PV) vs Future Value (FV), why?

Because they are linked to each other.

  • FV is the compounded value of PV.
  • PV is the discounted value at interest rate of FV.

Which can be illustrated in this graph, source link:

enter image description here

In the example below, where I replicated the way the example calculate PV (Column E the example from excel-easy, Loan Amortization Schedule) and in Column F we use Excel's build in function PV. You want to know the other way... therefore FV Column J.

Since they are linked they need to give the same Cash Flows over time (bit more tricky if the period/interest rate is not constant over time)!!

And they indeed do:

enter image description here

Payment number is the number of periods you want to look at (10 year * 12 payments per year = 120, yellow cells).

PV function is composed by:

rate: discount rate per period

nper: total amount of periods left. (total periods - current period), (12*30-120)

pmt: the fixed amount paid every month

FV: is the value of the loan in the future at end after 360 periods (after 30 year * 12 payments per year). A future value of a loan at the end is always 0.

Type: when payments occur in the year, usually calculated at the end.

PV: 0.05/12, (12*30)-120, 536.82 ,0 , 0 = 81 342.06

=

FV: 0.05/12, 120, 536.82 , 100 000.00 , 0 = -81 342.06
user_45525290
Related