Notifications
Q&A

A single formula to calculate the balance due on a loan

+1
−2

An as yet unanswered Q on Web Applications from the start of the year asks how to calculate the remaining amount of a loan at a given point in time, in a single formula. It uses sample data from the Amortisation sheet here shown below in A1:B5:

Data Sample

Why should this post be closed?

0 comments

1 answer

+1
−0

One way is with FV which will return the required result by directly referencing the given data, provided the third parameter (payment_amount) is fed to it which (as shown in Amortisation sheet) can be calculated with PMT.

Thus a single formula:

=-FV(B2/B4,B5,PMT(B2/B4,B3*B4,B1),B1)

Financial (and statistical) functions may be relatively prone to behave differently in different applications, but the same result may be achieved in the same way in Excel.

0 comments

Sign up to answer this question »