Loan Cash Flows Valuation in Excel using XNPV

A fixed interest loan generates periodic cash flows to the lender in the form of interest payments and principal repayments by the borrower. This loan asset on the books of the lender or the future cash inflows expected from the borrower can be valued at any point in time during the currency of the loan using the prevailing market interest rate as the discount rate. This valuation of known future cash flows can be done in excel spreadsheet by using Net Present Value (NPV) function.

Excel's XNPV is an efficient and easy to use function to do such cash flows valuation, when the known cash flows occur at irregular intervals - whether the interest paid is monthly, quarterly or any other frequencies and loan repayment is in regular or irregular instalments or bullet payment. All that is required for doing this valuation is to arrange the cash flows along with the dates these cash flows occur on, in two adjacent columns, type in XNPV function and link the discount factor, cash flows range and dates range - using this syntax: =XNPV(rate,values,dates). This spreadsheet demonstrates the calculation of valuation of loan cash flows in Excel using xnpv function.