An Example of Using Advanced Excel Applications Within the Scope of Financial Mathematics Calculations: Modelling of Calculations Related to Annuities in Microsoft Excel Program

Financial mathematics applications, form the mathematical basis for rational financial decisions within the scope of financial management. Carrying out financial management in line with the scientific rules is of vital importance for businesses to sustain their existence and carry out their activities effectively and efficiently. In this context, the ability to carry out financial management rationally is only based on the correct determination of the mathematical datas (etc. time value of money) that forms the basis for decisions. The use of technology in this area is very important and technology level in this area has evolved from financial calculators to computer programs and MS. Excel program is perhaps the most important application tool in this field according to its mathematical calculation capacity and capability. In this regard, the study reveals how to perform complex calculations regarding the present and future values of different annuity types in Excel. The calculation of all variables related to annuity calculations and the mathematical relationships between variables are discussed separately according to different annuity types is examined with a model which developed in Excel program within the scope of the purpose of the study. The study will provide guidance to academics related to the field and professionals working in the field of financial management by combining theoretical knowledge and practical applications.


Introduction
Compared to other areas of mathematics, the mathematics of finance has a relatively recent history.A great deal of research continues to be carried out in the field of financial mathematics and there is a strong demand from financial institutions for further progress and and development in this area (Tretyakov, 2013).The field of financial mathematics brings together theoretical knowledge and technological applications as well as different disciplines and with this structural feature, it can be characterised as an interdisciplinary field, and it can also be stated that it is an important analytical and solution-generating tool for financial markets (Feng, 2023).
From this point of view, the field of mathematics that deals with finance is called mathematical finance or quantitative finance because it analyses finance with mathematical methods (Petters & Dong, 2016).The main purpose of classical mathematical finance is to analyse financial transactions, so financial mathematics creates mathematical formulations for comparing monetary amounts that occur at different time points, and through these formulations, it evaluates financial transactions according to monetary movements (Romagnoli, 2016).Financial transactions involve numerical calculations and therefore the more complex they are, the more extensive and detailed mathematical formulations they require, and the basic principles on which the calculations are based must first be established.In line with the basic principles, variables and relationships between variables are determined, and the common component used in making calculations in all financial transactions is interest, and interest determines the time value of money (Broverman, 2010).It is stated that the basic element in all calculations in the field of financial mathematics is the time factor, the purchasing value of money is formed depending on the time it matures, and financial mathematical calculations are made taking into account the time factor, and in addition to this, classical financial mathematics calculates the interest of capital obtained through debt on the one hand, while analysing the development of capital over time on the other (Luderer, 2021).A business cannot make any decision in the field of financial management on a rational basis without considering the time value of money.First of all, accurate data should be obtained by making calculations about the time value of money and then these data should be analysed.In this context, perhaps the most important issue for the rational execution of financial management is the correct and effective calculation of annuities.
In an annuity, there should be a series of cash flows and these cash flows should be equal and equally spaced in time series (Rayburn, 1996).An annuity consists of a series of equal cash flows and is realised at regular intervals and can be made at the beginning or end of each period (Weston, Besley & Brigham, 1996).When defining the concept of annuity, it is necessary to explain the concept of time series.
In a time series, there are different time points from the past to the future and the time series gives the measurements of the relevant variable at these different time points as a series (Begg, Fischer & Dornbusch, 1991).Annuities are cash flows that occur at fixed intervals within a series of cash flows over a certain period of time, and these cash flows can be in fixed amounts or in different amounts (Adams et al., 2003).Annuities are divided into two types according to the point at which the cash flow is realised during the period, annuities at the beginning of the period are called cash annuities, annuities at the end of the period are called normal annuities and the type in which the cash flow is realised at the end of the period is much more common in finance (Shapiro and Balbirer, 2000).In order to perform financial mathematical calculations, including annuity calculations, accurately, effectively and quickly, Excel programme should be used effectively.In this study, a sample Excel model was developed and calculations were performed through the model.

Method
In this section, we will create an Excel model to solve complex annuity problems and show how to solve the problem using this Excel model and the Solver add-in.There is no ready-made Excel formula or function for solving problems involving grace period repayment schedules (deferred annuities), and such problems can only be solved by creating a model.
And in the context of an example question, it would be useful to show how the Excel programme and advanced Excel applications are used to solve annuity problems.The loan amounting to USD 1.000.000will be utilised under the following conditions: Loan repayments will be made in equal amounts every six months and at the end of the maturity.The maturity of the loan 5 years and the first 2 years will be grace period.The instalments to be paid within three years will be 300.000USD.Under these conditions, what percentage should the interest rate be for the loan to be repaid?
In order to solve this problem in Excel, in the first stage, it is necessary to create the Excel model described in detail below and the Excel formulas entered within the scope of the model must be included, shown as below: In Cell C3: Annual interest rate will be calculated using Excel Solver add-in.In Cell C4; Annuity amount will be entered.In cell C5; The loan amount to be used will be entered.In Cell C6; "=C3/C7" formula will be entered.In this way, the interest rate will be calculated from the annual interest rate.Cell C7 will calculate the number of circuits according to the selection to be made in cell C10 and the formula used in this cell is shown below: IF(C10=E11;365;IF(C10=E12;52;IF(C10=E13;12;IF(C10=E14;4;IF(C10=E15;3;IF(C10=E1 6;2;IF(C10=E17;1;" Enter Circuit Frequency"))))))) In Cell C8; The number of the payment period will be entered.In Cell C9: Non-payment period, that is, the number of non-payment periods will be entered.In Cell C10; Circuit frequency will be selected from Combo-Box.In Cells G3 and H3, equation has been created for Solver application.Equation equation will be used in solver application.In G3 Cell; "=C5*(((1+C6)^(C8+C9))*C6)" formula will be entered.In Cell H3, the formula "=C4*(((1+C6)^C8)-1)" will be entered.
Commands to be given from the Solver Menu: Select C6 in the Set Objective cell, thus commanding the Solver to maximise Cell C6 according to the variables and constraints.Select cell C3 in the Variables section and add it by pressing the Insert button.Constraints: "G3=H3", "G3>=1" and "H3>=1" and after these entries are made, the Excel Model will be ready for calculation.As a result, in order for a loan of $1,000,000 to be repayable with a grace period of 2 years followed by 6 monthly instalments over 3 years, the loan should be used with an annual interest rate of 16.57%.

Conclusion
Financial mathematics has evolved from simple interest calculations with paper and pencil, to calculators, from calculators to financial calculators, and with the development of technology, to the present day where analyses are made with computer programs.For these calculations, we have today a programme such as Excel, which is complex and excellent in many respects.At this late stage of development in the field of finance, the issue of how to use the possibilities of the Excel programme in a detailed and effective way is an issue that needs to be studied intensively.
Studies by Baird (2013), Barry (2004), Bauer (2006), Funck and Stretcher (2013), Liang and Martin (2008), MacDougall and Follows (2006), Payne and Tanner (2011) and Zhang (2014) show how Excel programme is used in various fields related to finance and these studies support the importance of using Excel in finance.
A business has to recognise value differences arising from the time value of money in its short-term and long-term activities.Differences in the time value of money can result in the loss of monetary value generated by a large number of people and maintaining and expanding the liquidity of the business is the primary task of financial management.Therefore, making all these calculations regarding the time value of money accurately and effectively, evaluating dozens of different alternatives correctly, and carrying out this process dynamically depending on the method and methodology is a vital element in financial management.For this reason, problems related to financial mathematics should be solved not only by using the relevant formulas in Excel, but also by building models containing the relevant formulas in Excel.And finally, a business that is proficient in the use of Excel models will be able to easily reach rational solutions in situations involving complex financial problems.

Figure 1 :
Figure 1: Images of Solver add-in entries made within the scope of the Excel model

Figure 2 :
Figure 2: Illustration of the final solution obtained within the Excel model