How to Use an Excel Data Table for “What-if” Analysis | Data Table in excel

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.

 

 

what if analysis data table

what if analysis data table
8 views

Leave a Reply

Your email address will not be published.

Newsletter Signup

Subscribe to our weekly newsletter below and never miss the latest product or an exclusive offer.