what-if analysis data table
How to Use an Excel Data Table for “What-if” Analysis | Data Table in excel
How to Use an Excel Data Table for “What-if” Analysis | Data Table in Excel About the video: In this video, we have taken an example of the PMT formula to find out the use of the data table in our excel sheet. PMT function is used to find out installment of our loan amount now after finding out our installment of the loan amount of particular time and particular interest if we want to analyze installment if our time and interest varies what will be interest under such condition we can use data table to analyze our installment amount a great tool for data analysis.
How to use data table in Excel
To use data table in Excel we have taken to find loan amount in Excel in the example below
- Loan Amount : 10,00000
- Interest 8% yearly
- Time : 5 years
First we use PMT function as shown below in the picture
=PMT(rate, nper, pv, [fv], [type])
=PMT(rate/12, nper*12, pv, [fv], [type])
Note: here we divide rate by 12 and nper we divide 12 so we can get monthly installment of money . Fv blank type 0 or leave blank.
After finding the PMT.
Next is to create a table of rate like 6% 7% 8% etc.
- Select PMT installment such as =B7.
- Select the table area.
- Go to date table from table
- Now in column select B7
- Installment will appear.