A single formula to calculate the balance due on a loan


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?


1 answer


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:


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.


Sign up to answer this question »