Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel for Mac 2011 Excel Mobile More...Less Managing personal finances can be a challenge, especially when trying to plan your payments and savings. Excel formulas and budgeting templates can help you calculate the future value of your debts and investments, making it easier to figure out how long it will take for you to reach your goals. Use the following functions:
Figure out the monthly payments to pay off a credit card debt Assume that the balance due is $5,400 at a 17% annual interest rate. Nothing else will be purchased on the card while the debt is being paid off. Using the function PMT(rate,NPER,PV) =PMT(17%/12,2*12,5400) the result is a monthly payment of $266.99 to pay the debt off in two years.
Figure out monthly mortgage payments Imagine a $180,000 home at 5% interest, with a 30-year mortgage. Using the function PMT(rate,NPER,PV) =PMT(5%/12,30*12,180000) the result is a monthly payment (not including insurance and taxes) of $966.28.
Find out how to save each month for a dream vacation You’d like to save for a vacation three years from now that will cost $8,500. The annual interest rate for saving is 1.5%. Using the function PMT(rate,NPER,PV,FV) =PMT(1.5%/12,3*12,0,8500) to save $8,500 in three years would require a savings of $230.99 each month for three years.
Now imagine that you are saving for an $8,500 vacation over three years, and wonder how much you would need to deposit in your account to keep monthly savings at $175.00 per month. The PV function will calculate how much of a starting deposit will yield a future value. Using the function PV(rate,NPER,PMT,FV) =PV(1.5%/12,3*12,-175,8500) an initial deposit of $1,969.62 would be required in order to be able to pay $175.00 per month and end up with $8500 in three years.
Find out how long it will take to pay off a personal loan Imagine that you have a $2,500 personal loan, and have agreed to pay $150 a month at 3% annual interest. Using the function NPER(rate,PMT,PV) =NPER(3%/12,-150,2500) it would take 17 months and some days to pay off the loan.
Figure out a down payment Say that you’d like to buy a $19,000 car at a 2.9% interest rate over three years. You want to keep the monthly payments at $350 a month, so you need to figure out your down payment. In this formula the result of the PV function is the loan amount, which is then subtracted from the purchase price to get the down payment. Using the function PV(rate,NPER,PMT) =19000-PV(2.9%/12, 3*12,-350) the down payment required would be $6,946.48
See how much your savings will add up to over time Starting with $500 in your account, how much will you have in 10 months if you deposit $200 a month at 1.5% interest? Using the function FV(rate,NPER,PMT,PV) =FV(1.5%/12,10,-200,-500) in 10 months you would have $2,517.57 in savings.
See alsoPMT function NPER function PV function FV function Need more help?What is the formula of APR?APR can be found with the formula, APR = ((Interest + Fees / Principal or Loan amount) / N or Number of days in loan term)) x 365 x 100. Is the annual percentage rate the same as the interest rate? No, APR is broader than the interest rate.
|