Using Excel formulas to figure out payments and savings
Microsoft Excel can help you manage your finances. Use Excel formulas to calculate interest on loans, savings plans, down payments, and more.
Look up values with VLOOKUP, INDEX, or MATCH - Microsoft …
This means that the column containing the value you look up should always be located to the left of the column containing the return value. Now if your spreadsheet isn't built this way, then do not use VLOOKUP. Use the combination of INDEX and MATCH functions instead.
PMT function - Microsoft Support
How to use the PMT function in Excel to calculate monthly loan payments based on constant payments and a constant interest rate.
PV function - Microsoft Support
PV, one of the financial functions, calculates the present value of a loan or an investment, based on a constant interest rate. You can use PV with either periodic, constant payments (such as a mortgage or other loan), or a future value that's your investment goal.
Use Goal Seek to find the result you want by adjusting an input …
You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal.
NPER function - Microsoft Support
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).
Using IF with AND, OR, and NOT functions in Excel
In Excel, the IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if that condition is True or False. =IF (Something is True, then do something, otherwise do something else)
FV function - Microsoft Support
Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on …
Calculate multiple results by using a data table
A great example is using the PMT function with different interest rates and loan periods (in months) to figure out how much of a loan you can afford for a home or a car. You enter your numbers into a range of cells called a data table.
ISPMT function - Microsoft Support
The ISPMT function returns the interest payment for a given period for this type of loan. To illustrate, the amortization table below uses an even-principal repayment schedule. The interest charge each period is equal to the Rate times the unpaid balance for the previous period.
Analyze Data in Excel - Microsoft Support
We can answer it! Simply select a cell in a data range > select the Analyze Data button on the Home tab. Analyze Data in Excel will analyze your data, and return interesting visuals about it in a task pane. If you're interested in more specific information, you can enter a question in the query box at the top of the pane, and press Enter.