Test Purpose: To provide a simple guide to develop calculations on some basic financial operations such as: simple and compound interest, present and future value of simple and compound interest. All this with the use of a simulator in Excel.
Methodology
This essay follows a very simple scheme for understanding and teaching the basic formulas of financial mathematics, to carry out some mathematical calculations, using the simulator in Excel. The notation used is based on Pastor (1999).
The teaching didactics for each of the topics covered is as follows:
The topic and its explanation are mentioned.
The formula that we are going to use is shown to solve the problem posed as an example.
The formula is developed, according to the topic in question.
The result is analyzed.
The simulator page is shown where the values are entered to solve the problems and the result is verified with the use of the simulator in Excel.
Simple interest
We are going to start by defining what simple interest is, which will give us the basis to understand the following topics, so, we can define it as: “The amount that a borrowed capital accrues at a certain rate and time, without performance". It is normally used for short-term operations and there are no
Formula capitalizations:
I = P * i * n
Where:
- I = is simple interest P = is capital i = is interest rate n = is the term that is handled
Example: Let us suppose that a person needs to request a small loan in order to pay an order to the supplier because it is not enough with what he has at that time, so he asks a popular bank for a loan for $ 50,000.00 to be paid in three months with a 18% annually. So, applying the formula it is as follows:
I = (50,000) (.18) (3/12)
I = (50,000) (.18) (.25)
I = $ 2,250.00
Which means that a person who asks for a loan in the conditions recreated in the example, will be paying an interest of $ 2,250.00 over the three months and in the end the person will pay $ 52,250.00 to pay off his loan to the popular fund. Simple interest is used in operations for short-term loans or investments where the terms are not greater than one year. This type of calculation is used to find out how much interest we will pay or receive at the end of a certain period.
Operations in the financial simulator:
Amount:
What we will see next will be how to determine how much we will pay or receive in total at the end of a certain period of time. We will call this final total from now on amount and we will identify it with the letter (S) for the handling and substitution in the corresponding formulas.
Formula: S = P (1 + in)
It is divided between the days that make up the ordinary (annual) interest that we will manage based on 360 days.
Example: Suppose you buy $ 30,000.00 in merchandise from your supplier for your grocery store, paying $ 12,000.00 cash upon delivery of the order and the rest to be paid in 4 months with an interest of 13.5% per year. How much will you have to pay your provider to settle your debt?
Applying the formula we have to:
S = $ 18,000.00 (1 + ((.135) (4/12)))
S = $ 18,000.00 (1 + ((.135) (. 333333)))
S = $ 18,000.00 (1 +.045)
S = $ 18,000.00 (1,045)
S = $ 18,810
Analyzing the previous scenario we have that, for the $ 18,000.00 that we have to owe to the supplier, after 4 months with an interest rate of 13.5%, we must pay the amount of $ 18, 809.99 to settle our debt.
Operations in the financial simulator:
It is important to make a parenthesis at this point to explain that it is common, that commercial and financial operations are determined in dates and not in months or years. Therefore, if we are going to carry out one of these operations, we have to convert the term (n) into the days that are determined.
For this we must divide the days that we will identify with the letter (t) applying the following formula, Formula:
Example: The soft drink company "Jarochito" sells you $ 5,000.00 in product, giving you 7 days to pay your order, if the interest applied by the company is 30%. How much will you have to pay to pay off your debt with "Jarochito"?
Applying the formula we have that;
As we can see in the previous problem, the term (n) is determined as 7 days in which the debt contracted with the soft drink provider must be settled, so the result of multiplying the interest rate by the term is divided by the base 360 to determine the conversion of the term in days. In the end we must pay $ 5,029.16 to settle our debt.
Operations in the financial simulator:
Now let's analyze another case:
A businessman in the commercial branch dedicated to the sale of dairy products and delicatessen, in the last 4 months has had an increase in sales of fresh cheese that he himself produces in his establishment, unfortunately he cannot satisfy said demand Because its productive capacity is limited, which is why it decides to quote machinery that will increase its production by 200%, that is, it would have 2 times more product when acquiring said equipment. The price of the machinery in the market does not vary much, so he decides to buy it from a supplier that sells the equipment for $ 30,000.00 in cash, and on credit with an interest rate of 21% to be paid in 12 months.
Well, the first thing we must determine are the conditions of the scenario, which would be as follows:
Scenario 1
Cash
Investment: $ 40,000.00
Sales $ 10,000 per month
Increase in sales to $ 20,000
Scenario 2
On credit
Investment: $ 40,000.00
Sales $ 10,000 a month
Sales increase to $ 20,000
Interest 21%
Procedure development:
6-month term. S = P (1 + in)
So:
S = $ 40,000.00 (1 + ((.21) (6/12)))
S = $ 40,000.00 (1 + ((.21) (. 5)))
S = $ 40,000.00 (1 +.105)
S = $ 40,000.00 (1,105)
S = $ 44,200.00
At the end of the 12 months, the entrepreneur must pay a total of $ 44,200 for the equipment purchased, as shown by applying the Future Value formula, which is basically the same as the Amount. From these results, the employer can make a decision.
Operations in the financial simulator:
When we want to pay off the debt before the agreed date:
If 4 months after acquiring the machinery on credit, the increase in sales gives us the ability to pay for the equipment in advance, then how much would we have to pay for the equipment?
To solve the previous question we must apply a new formula to determine the Present Value of our debt.
So replacing the data from the previous problem we have to,
To better understand the previous case, we must mark an imaginary timeline that helps us better understand how to propose the solution.
If we pay our equipment 2 months before, we must discount the interest that will not be generated in those two months, so the advance payment remains at $ 42, 843.29 with a discount of $ 1,356.70.
Operations in the financial simulator:
Compound interest
We can define it as: "The amount that a borrowed capital accrues at a certain rate and time, where the return is capitalized". It is normally used for long-term operations.
Formula:
S = $ 136,341.07
S = P (1 + i) n
Where:
- S = is the compound amount P = is capitali = is the interest rate for the compounding period n = compounding periods
Example: Suppose that a person deposits 100,000.00 in an investment that generates an interest of 15.6% nominal, which is capitalized monthly. How much do you get at the end of the two years?
S = P (1 + i) n S = 100,000 (1 + 0.013)) 24 S = 100,000 (1,013)) 24
S = 100,000 (1.363410671) S = $ 136,341.07
Same case with the formula:
S = $ 136,341.07
Operations in the financial simulator:
Check with P
Verification of present and future value with compound interest
conclusion
We can finish by saying that the financial simulator is a very useful tool to carry out operations of all kinds, especially those related to the topics covered in this document.
The financial simulator allows us to recreate the possible scenarios of payment to the supplier, helping us in choosing the option that best suits our interests, always seeking the commercial relationship with customers and suppliers through fair debt payment.
