Laboratory work No. 2
Working with financial functions.
What-if analysis
Goal of the work: Learn to work with Excel financial functions
and perform what-if analysis
1 Financial functions in economic calculations
2 Forecasting using What-if analysis
Financial functions in economic calculations
PMT function. Calculation of the monthly loan payment amount
The PMT function determines the periodic payment amount for an annuity based on constant payment amounts and constant interest rates.
Example 1 Determine the monthly payment if the bank provides a loan of 140,000 rubles. with installments of 5 years at 8.5% per annum with monthly payments. The last payment should be 10,000 rubles.
Let's enter the data into the Excel table according to Fig. 1)
1 Select cell AT 6 and click on the Insert function button (sign f x to the left of the formula bar). The Function Wizard window will appear, select the Financial category.
2 Click on the PMT function, drag the PMT window to an empty space on the screen , to free the table and
Figure 1 Calculation of annuity fill in its fields:
▪ Field Bid- this is the percentage per month,
enter 0.085,
▪ Nper– number of payment periods, i.e. 5 years*12 months, enter 5*12
▪ NZ– the total amount of all payments from the current moment, enter 140000,
▪ Bs– future value, enter 130000 with a “-” sign, because We pay, not the bank,
§ Type– payment at the end of the month, so enter 0 or nothing.
3 Press OK.
Result: about 2738 rub. You need to pay monthly to pay off 130,000 rubles. for 5 years (at the end of the term the last payment is another 10,000 rubles)
2 Forecasting using What-if analysis
What-if analysis allows you to predict the value of a function (mathematical, financial, statistical, etc.) when its arguments change. There are three ways to predict values: using data lookup tables, using scripts, and using parameter selection and finding a solution.
1 way. Data lookup table – This is a range of cells that shows how changing the lookup values affects the result returned by the formula. If a cell contains a formula that contains elements from other cells, then changing the value in one or more cells will change the result in the cell containing the formula.
Example 2 The company made a loan for 80,000 rubles. for a period of 3 years. Define:
Monthly payments at interest rates of 7%, 8% and 9% per annum,
Monthly payments at an interest rate of 5%, a loan term of 5 years and a loan amount of RUB 100,000.
1 Let us introduce a substitution table in the form (Fig. 2):
Figure 2 Substitution table
2 Enter the PMT payment formula (B3/12;B4*12;B5) into cell D2 manually or through the PMT window from the Function Wizard (see example 1), the calculated value of the function -2470.17 rubles will appear in D2.
3 Let's change the value of cell B3 by 8%, and in D2 we get the payment amount -2506.91 rubles.
4 Let's change the value of cell B3 by 9%, we get in D2 the payment amount -2543.98 rubles.
5 Let's simultaneously change the values of cells: B3 by 5%, B4 by 5 and B5 by 100,000, we get in D2 the payment amount -1887.12 rubles.
The substitution table must contain in one of the cells formula.
Method 2. Scenario - it is a set of wildcard values used to predict the behavior of the model. You can create and save several different scenarios on one Excel sheet and switch to any of them to view the results and choose the best one.
Example 3 Let's write out the options for substituting data from points 2 and 3 of example 2 in the form of scenarios.
To create a script you need to do the following:
1 From the Tools menu, select the Scripts command.
2 In the Script Manager window that opens, click the Add button.
3 Enter a name for the scenario, for example "Rate 7%".
4 In the Changeable cells field, specify those cells (separated by a colon) that you are going to change, in this case, cell B3.
5 Press the button OK.
6 In the Scenario Values dialog box that opens, for each cell being changed, enter a new value or formula; in this case, enter the number 0.07 in B3. Click the button OK. It is advisable to save the initial what-if model as a script, naming it, for example, “Starting Values”. Otherwise, when you define new changeable cells, the original data will be lost.
To view the script you need to use the button Withdraw in the Script Manager window. By clicking the Summary button in the Scenario Manager dialog box, you can obtain a summary report in a separate worksheet called Scenario Structure, showing the impact of different scenarios on one or more result cells. The "+"("-") signs on the left and top allow you to expand (collapse) individual sections of the report. Changeable fields are highlighted in grey.
3 way. Selection of parameter. When selecting a parameter, the value of the influencing cell (parameter) changes until the formula depending on this cell returns the specified value.
Example 4 Condition of example 1. The company can pay no more than 2500 rubles monthly. Determine what the last payment should be for this.
1.Select cell B6:
2.In the Tools menu, select the Parameter Selection command.
In the Parameter Selection window:
In the Set in cell field – B6 is entered,
In the Value field - enter -2500
In the Changing cell value field, enter B3 (last payment cell),
Click OK.
Result: last payment = -27716 rub.
When selecting a parameter, one of the cells must also contain formula , since the table is a lookup table.
The Search for Solution command from the Tools menu is used to simultaneously select several parameters in order to maximize or minimize the contents of the target cell and is discussed in detail in laboratory work No. 7 (Excel-7).
Control questions
1 How to display the Function Wizard application?
2 What operation does the PMT function perform, what is entered into its fields Norm, Nper, Nz, Bs, Type?
3 Purpose and methods of “What if” analysis?
4 What is a “Table of Substitutions”, what is the composition of its cells?
5 What is a script, how to create it, view it, and get the final report on a separate sheet?
6 The essence of the operation Selection of a parameter, how is it performed?
Tasks
1 Complete the task of example 1, changing the loan amount to 140000· n, Where n- student number in the teacher’s journal. Do the same for the new loan amount, changing the annual interest from 8.5% to 5%, and the loan term from 5 to 10 years.
2 Perform a “What-if” analysis using the substitution table in Example 2, changing the loan amount to 80000·n, where n is the student number in the teacher’s journal.
3 Formulate in the form of scenarios all the operations from paragraph 1 (two scenarios) and paragraph 2 (four scenarios) of this assignment for laboratory work.
4 Complete the task of example 4, changing the monthly payment amount to n·100.
1Title, purpose, content of the work
2 Written answers to security questions
Have you ever taken out a loan from a bank? Then this article is for you. When evaluating and analyzing loan options, it is necessary to obtain final values (how much will you have to pay?) for different sets of initial data (in this case, interest rates). One of the advantages of the MS Excel spreadsheet processor is the ability to quickly solve such problems and automatically recalculate the results when the source data changes. Let's say you are planning a project and for this you take out a loan from a bank. When is it better to repay the loan, what interest rates should I choose? To solve such problems, MS Excel uses Lookup table. The use of this tool occurs in this way.
Possible values for one or two function arguments must be presented in the form of a list or table. For one argument, the list of initial values is specified as a row or column of a table. MS Excel represents these values into a formula (function) specified by the user, and then substitutes the results into the appropriate row or column.
When using a table with two variables, the values of one of them are located in a column, the other in a column, and the result of the calculation is based on one or more formulas, and a table for two variables containing calculations for one formula.
In this article we will look at a substitution table for one variable. For a table with two variable values, see the following article.
Let's say you take out a loan of 100 thousand rubles for a period of 5 years and determine monthly payments at different interest rates.
To solve this problem it is used Lookup table MS Excel. First, we write down the initial data - loan amount, term, interest rate according to the figure.
In cell D7 we enter the formula for periodic constant payments on the loan, provided that the amount must be repaid during the loan term: = PMT (C4/12;C3*12;C2)
We divide the interest rate by 12 in the case of monthly payments and select the cell format as percentage - the interest rate in this case is written thus: 12% - 0.0125 - cell format - percentage.
Nper– number of payment periods. If the period is in years, then to calculate monthly payments we multiply by 12.
Ps– indicate the amount we are borrowing (in our case, it is 100,000).
Bs And Type– optional parameters. Bs– the future value or cash balance to be achieved after the last payment; is assumed to be 0 if no value is specified. Type– a boolean value (0 or 1) indicating whether the payment should be made at the end of the period or at the beginning of the period.
Select the range of cells containing the values of interest rates and formulas for calculation - C7:D18.
Run the command. A dialog box will appear on the screen Data table. (see figure). This window is used to specify the work cell that is referenced by the calculation formula. In our example this is the cell C4, which must be specified in the field Substitute values by row into:.
If the source data is located in a column, then the link to the work cell must be entered in the field Substitute values by columns into:. After pressing the button OK the program will fill the column with results. The resulting numbers have a “-” sign.
Let's say that you wanted to determine which part of the payment goes to repay the interest on the loan, and which part goes to the interest on the loan. To do this, in the next column, in the cell E7 you need to enter the formula: = PROCESS PAYMENT(C4/12;1;C3*12;C2) (see figure).
Then run the command again Data – What If Analysis – Data Table, having previously selected the required range of cells. After pressing the button OK The table Interest payment for 1 month appears. (see figure). If these numbers don’t scare you, then you can safely go to the bank for a loan.
Good luck calculating interest payments
Excel is a truly powerful tool due to its unique versatility and ability to solve problems for people from different professional fields. Excel is indispensable for managers and economists, entrepreneurs and financiers, accountants and analysts, mathematicians and engineers. Its versatility is given by specific built-in functions that certain specialists use in their calculations.
One of the largest and most popular feature categories is financial. The latest version of Excel has 55 functions that fall into this group. Many of them are specific and narrowly focused, but some can be useful to almost everyone. One of these basic functions is PMT.
As the official certificate says,The PMT function returns the periodic payment amount for an annuity based on constant payment amounts and a constant interest rate. If you are confused by the specific term "annuity" - do not be alarmed. In other words, using the PMT function, you can calculate the amount that will need to be paid every month, provided that the interest on the loan does not change and payments are made regularly in equal amounts.
Function Syntax
The function has the following syntax:
PMT(rate; nper; ps; [bs]; [type])
Let's look at all the arguments one by one:
- Bid.Required argument. Represents the interest rate for the period. The most important thing here is not to make a mistake in recalculating the size of the bet for the required period. If you plan to repay the loan in monthly payments, and the annual rate, then it must be converted into a monthly rate, dividing by 12. If, for example, the loan is repaid once a quarter, then the annual rate must be divided by 4 (and thus get the rate for 1 quarter ). The rate can be specified as a percentage or hundredths.
- Nper.Required. This argument represents the number of billing periods (how many times payments will be made to repay the loan). Like the rate, this argument depends on which settlement period is used for calculations. If the loan is received for 5 years with payments once a month, thenNper = 5*12 = 60 periods . If for 3 years, with payments once a quarter, thenNper = 3*4 = 12 periods .
- Ps. Required. The loan amount, that is, the amount of debt that will need to be repaid with future payments.
- [bs].Optional. The amount of debt that must remain unpaid after all billing periods have expired. Typically this argument is 0 (the loan must be repaid in full). Since the argument is optional, it can be omitted (in this case it will be taken equal to zero).
- [type].Optional. Indicates the moment of payment - at the beginning or at the end of the period. For the first case, you need to specify one, and for the second, zero (or skip this argument altogether). In most cases, the second option is used - payments at the end of the period, which means that most often this argument can be omitted.
A special feature of the function syntax is to indicate the direction of the cash flow. If the cash flow is incoming (for example, the amount of the loan received, specified in the argument Ps), then it must be indicated as a positive number. Outgoing flows, on the contrary, are indicated as negative numbers (for example, after calculation, the PMT function will return a negative result, since the amount of the loan payment is an outgoing cash flow).
Examples of using
Task 1. Calculating the amount of loan payments
Let's assume that a bank received a loan in the amount1 000 000 rub. under 17,5% per annum for a period 6 years. The loan will be repaid in equal monthly payments throughout the loan term. By the end of the term, the entire amount of the debt will be paid. The first payment will be made at the end of the first period. You need to find the monthly payment amount.
So, we know the annual rate, and the loan will be repaid monthly. This means that to calculate, we will need to convert the annual rate into a monthly rate, dividing 17.5% by 12 months.In the first argument we write 17,5%/12 .
The loan was received for 6 years. Paid monthly. This means the number of payment periods = 6*12.In the second argument we write 72 .
In the third argument we write the loan amount. It is equal to 1,000,000 rubles. (for the borrower this is an incoming cash flow, we indicate it as a positive number).
We will omit the fourth argument, since the amount will be fully repaid by the end of the term. We will also omit the fifth argument, since payments are made at the end of the period.
The formula will look like:
PLT(17.5%/12;72;1000000)
The result of the calculation is-22526.05 RUR. The number is negative because the loan payment is an outgoing cash flow for the borrower. This is the amount that will need to be paid every month to repay the loan described in the conditions.
To calculate the amount of the final overpayment, you need to multiply the monthly payment by the number of periods (Nper) and subtract the loan amount (Ps) from the result.
Task 2. Calculation of the deposit replenishment amount to accumulate a certain amount of funds
The bank has opened a replenishable deposit with a rate of 9% per annum. You plan to deposit the same amount of money every quarter (for example, part of the quarterly bonus received) with the goal of accumulating exactly 1,000,000 rubles in the account in 4 years. Question: How much should I top up my account every quarter?
We indicate the first argument as 9%/4 (since the annual rate must be converted to a quarterly rate), the second argument = 4*4 (4 years, 4 quarters - a total of 16 contributions). The third argument is the loan amount. We take it as 0, since we didn’t take anything. The fourth argument is future value. We indicate the amount we want to save (RUB 1,000,000). We omit the fifth argument again (payments at the end of the period, this is the most common situation).
We get the formula:
PMT(9%/4;4*4;0;1000000).
Calculation result:-52,616.63 rub.This amount must be deposited into the specified deposit every quarter in order to have a million rubles in the account after four years.
Total amount of deposited funds = 52616.63 * 16 = 841,866.08 rubles. The rest is accumulated through interest.
Features Features
When using the function, pay attention to the following points:
- the function is intended only for annuity payments (that is, equal payments at regular intervals);
- the function works according to the classic credit model, which does not always coincide with what modern credit organizations offer. In many cases, lending conditions will not allow you to successfully apply the PLT function to them and you will have to write a separate model and look for a solution usingParameter selection or Finding a solution(the creation of a similar model can be ordered on our website - tDots.ru);
- the function takes into account the payment of principal and accrued interest, but does not take into account various additional charges, commissions, taxes and fees, etc.;
- the sign of the number (positive or negative) specifies the direction of the cash flow. The flow from the creditor to the debtor (for example, the loan amount) will have one sign, and the flow from the debtor to the creditor (for example, the monthly repayment amount) will have the opposite sign (it doesn’t matter whether it’s plus or minus).
You can support our project and its further development .
You can ask your questions about the article through our feedback bot atTelegram:
Is there a function in PHP similar to PLT() in EXCEL
Description of this function in EXCELa help. Does anyone know how to implement this in PHP?
================
PMT()
Returns the periodic payment amount for an annuity based on constant payment amounts and constant interest rate.
Syntax
PLT(rate;nper;ps;bs;type)
For a more detailed description of the arguments of the PMT function, see the description of the PS function.
Rate - the interest rate on the loan.
Nper - the total number of loan payments.
Ps is the value reduced to the current moment, or the total amount that is currently equivalent to a series of future payments, also called the principal amount.
BS - the required value of the future value, or the balance of funds after the last payment. If the bs argument is omitted, then it is set to 0 (zero), i.e. for a loan, for example, the bs value is 0.
Type - a number 0 (zero) or 1 indicating when the payment should be made.
Type When to pay
0 or omitted At the end of the period
1 At the beginning of the period
Notes
The payments returned by the PMT function include principal and interest payments, but do not include taxes, reserve charges, or fees sometimes associated with the loan.
Make sure you are consistent in your choice of units for specifying the rate and nper arguments. If you are making monthly payments on a four-year loan at 12 percent interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for the rate argument and 4 for the nper argument.
Advice
To find the total amount paid during the payout interval, multiply the value returned by the PMT function by nper.
Example 1
Instructions
1
2
3
4
A B
Data Description
8% Annual Interest Rate
10 Number of months of payments
10000 Loan amount
=PLT(A2/12; A3; A4) Monthly payment amount for the specified loan (-1,037.03)
=PLT(A2/12; A3; A4; 0; 1) Monthly payment amount on the specified loan, excluding payments made at the beginning of the period (-1,030.16)
Example 2
The PMT function can be used to calculate payments on annuities other than loans.
To make this example easier to understand, copy it onto a blank sheet of paper.
Instructions
Create a blank workbook or sheet.
Highlight an example in the help section. Do not highlight the row or column heading.
Highlighting an example in the help.
Press CTRL+C
On the worksheet, select cell A1 and press CTRL+V.
To switch between viewing the results and viewing the formulas that return those results, press CTRL+` (apostrophe) or on the Tools menu, point to Formula Dependencies and select Formula Validation Mode.
1
2
3
4
A B
Data Description
6% Annual Interest Rate
18 Estimated number of years to keep savings
50,000 Savings requirement after 18 years
Formula Description (result)
=PLT(A2/12; A3*12; 0; A4) Required monthly payment amount to receive 50,000 at the end of the eighteen-year period (-129.08)
Note. To get the monthly interest rate, divide the annual rate by 12. To find the number of payments, multiply the number of years of the loan by 12.