When know the other loan factors, such as the interest rate and the number of payment periods and amount to pay on each period. You can use PV fuction in Excel to determine how much you can borrow when you already know how much you can pay each month and how long you can make payments.
The PV() Returns the present value of a loan.
PV(rate, nper, pmt, [fv], [type])
- Rate The interest rate per period. For example, if you obtain an automobile loan at a 10 percent annual interest rate and make monthly payments, your interest rate per month is 10%/12, or 0.83%. You would enter 10%/12, or 0.83%, or 0.0083, into the formula as the rate.
- Nper The total number of payment periods in an annuity. For example, if you get a four-year car loan and make monthly payments, your loan has 4*12 (or 48) periods. You would enter 48 into the formula for nper.
- Pmt The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. For example, the monthly payments on a $10,000, four-year car loan at 12 percent are $263.33. You would enter -263.33 into the formula as the pmt. If pmt is omitted, you must include the fv argument.
- Fv The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). For example, if you want to save $50,000 to pay for a special project in 18 years, then $50,000 is the future value. You could then make a conservative guess at an interest rate and determine how much you must save each month. If fv is omitted, you must include the pmt argument.
- Type The number 0 or 1 and indicates when payments are due.
Using this function requires three must give inputs. Those are:
- Interest rate
- The number of payment periods
- and the monthly payment amount
Make a excel sheet like the one shown below:
Put this Formula in the cell “B2”
Where PV is the Principle(we want to know), B3 is the annual interest rate(we have to divide it with 12 to get monthly), B4 is how many times we have to pay and B5 is the amount we have pay in each period.
Thats it! Now you know that you can borrow $5,841 now if you want pay $500 with a interest rate of 5% per annum for 12 months.