Home        Online Calculators        Personal Finance        Corporate Finance        Spreadsheet Models        Derivatives   
Bond YTM Calculator
Zero Coupon Bond Yield
XIRR Calculation in Excel
MIRR Calculation in Excel
Loan Cash Flows Valuation
Rate Conversion Formulae
Interest Rate Converter

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.