Examples of the BS function in Excel with a fixed interest rate. BS - encyclopedia of technology - what it means, description, photo, interpretation, definition What does BS mean

BS(rate;nper;plt;[ps];[type])

For more information about the arguments of the BC function and other functions related to annuities, see the description of the PV function.

The arguments of the BS function are described below.

Bid- required argument. Interest rate for the period.

Nper- required argument. The total number of payment periods for the annuity.

Plt Required. Payment made in each period; this value cannot change during the entire payment period. Typically, the "plt" argument consists of the principal payment and the interest payment, but does not include other taxes and fees. If omitted, the "ps" argument is required.

Ps- optional argument. Present value, i.e. the total amount that is currently equivalent to a number of future payments. If the "ps" argument is omitted, the value 0 is assumed. In this case, the "plt" argument is required.

Type- optional argument. The number 0 or 1 indicating the due date. If the "type" argument is omitted, the value 0 is assumed.

Notes

Make sure that the units used for the "rate" and "nper" arguments are consistent. At monthly payments for a four-year loan at the rate of 12 percent per annum, use the value 12%/12 as the “rate” argument and 4*12 as the “nper” argument. For annual payments on the same loan, use 12% for rate and 4 for nper.

All arguments that match the payouts cash(for example, savings deposits) are represented by negative numbers, and received (for example, dividends) are represented as positive numbers.

Examples

Copy the sample data from the following table and paste it into cell A1 of a new Excel worksheet. To display the results of formulas, select them and press F2, then press Enter. If necessary, change the width of the columns to see all the data.

62 0

(Berezina, synchronous) - large-caliber (12.7 mm) aviation synchronous machine gun designed by M. E. Berezina. Created in 1939 and served as the basis for the development of the widespread UB aircraft machine gun.

Meanings in other dictionaries

BREM-1

Armored repair and recovery vehicle BREM-1 The combat repair and recovery vehicle BREM-1 is designed to evacuate emergency tanks and other equipment from the action area to collection points for damaged vehicles or to shelter, evacuate stuck equipment and provide assistance to crews during repairs and maintenance tanks in the field. Armored repair and recovery vehicle BREM...

BREM-2

Armored repair and recovery vehicle BREM-2 was a mobile vehicle technical support combat units armed with BMP-1, BMP-2 and their modifications. It was created on the basis of the chassis of the BMP-1 infantry fighting vehicle and entered service in 1985; its mass production began in 1986. The vehicle was equipped with a hydraulic boom crane, a load...

BT-2

The first tanks of the BT series, created in the fall of 1931, were branded BT-2. In the same year, three such vehicles took part in a military parade on Red Square. The first models of tanks, produced in several versions before 1933, had a coaxial installation of two machine guns in the turret. In the second series, a 37-mm cannon with a shoulder rest for aiming at the target and one DT machine gun in a ball were installed in the turret...

The BS function in Excel calculates the future value of an investment, assuming that the interest rate is constant (does not change over time), and returns the corresponding value. The function can be used in cases where a single payment is made at the end of the term, as well as when the total amount is divided into several fixed payments.

Examples of using the financial function BS in Excel

The investor made a deposit with a monthly capitalization in the amount of 100,000 rubles at 13% per annum for a period of 4 years. How much money can he withdraw from his deposit account upon expiration of the agreement with the bank?

Initial data:

Formula for calculation:

Description of the arguments:

• B3/12 – rate for the period (capitalization is carried out monthly);
• B4 – number of periods of deposit capitalization;
• 0 – payment amount for the capitalization period (an unknown value within the framework of this problem, therefore the value is 0);
• B2*(-1) – initial deposit amount (investment, which must be a negative number).

Calculation results:

After 4 years, the investor will receive 167,733 rubles.

Calculation of the amount of loan debt as of the 30th repayment period

The borrower took out a loan from a bank at 26% per annum in the amount of 220,000 rubles for a period of 3 years with a monthly fixed payment. What will be the amount owed by the borrower at the end of the 30th payment period?

Initial data:

Formula for calculation:

BS(B3/12;30;PLT(B3/12;B4;B2);B2)

Description of the arguments:

• B3/12 – monthly interest rate;
• 30 – period number for calculating the debt balance;
• PMT(B3/12;B4;B2) – a function that returns the amount of the monthly payment;
• B2 – loan body.

Result:

The actual debt for the loan at the end of the 30th month will be approximately 49,372 rubles.

Comparative investment analysis of bank deposit conditions

The depositor received deposit offers from two banks with different conditions:

1. The rate is 12% per annum, capitalization is monthly.
2. The rate is 33% per annum, capitalization is quarterly.

Determine which of the offers is more profitable if the deposit amount is 100,000 rubles, the contract period is 2 years.

Initial data:

Formula for calculation:

Using the IF function, we determine in which case the future value will be greater and return the corresponding value. Result:

Let us display the results of calculations of the BS functions and the difference in amounts:

As you can see, despite the high annual rate, the first bank made a more advantageous offer, since under the terms of the proposed agreement, capitalization is monthly. That is, the more often capitalization occurs, the faster the deposit amount increases.

Features of using the financial function BS in Excel

The BS function is used along with other financial functions (PS, PLT, NPER and others) and has the following syntax:

BS(rate;nper;plt;[ps];[type])

Description of the arguments:

• rate – an argument that takes a numeric or percentage value of the rate for the specified period. Required. If the annual rate is used according to the condition, it is necessary to recalculate using the following formula: R=Rg/n, where Rg is the annual rate, n is the number of periods.
• nper – a numerical value characterizing the number of payment periods. The argument is required. If the loan was taken out for a period of 3 years, payments for which must be made every month, the nper argument should take the value 3*12=36 (12 are months in a year).
• plt – a numerical value characterizing a fixed payment amount for each period. The argument is required. If the payment for the period is an unknown quantity, the argument pmt can take the value 0, but the next argument is specified explicitly.
• [ps] – present value at the moment. For example, when a borrower takes out a loan from a financial institution, the body of the loan is the present value. By default, the [ps] argument is 0, and pmt must be a non-zero value.
• [type] – a numerical value characterizing the type of payments: at the end or beginning of the period. Accepts only two values: 0 (if not explicitly specified) and 1.

Note 1:

Note 2: the BS function is also used to determine the balance of debt on a loan with an annuity payment schedule, and additional interest and commissions will not be taken into account. The annuity schedule assumes a fixed repayment amount for each payment period (consists of interest and the loan body).