Month: January 2023

Methodology Compound Interest Calculation (Updated October 2023)

A formula that can be used for calculating compound interest

A = P( 1 + (r/n) )^nt ; R = r * 100

Where:
A= Total Accrued Amount (Principal + Interest)
P = Principal Amount
R = Rate of Interest per year as a decimal 
; r = R/100 so 4% is 4 and r would be 0.04
t = number of periods
n = compounding period
(^ indicates to the power of)

Note: Remember BODMAS when calculating with variables

Reasoning – this breaks interest down into individual compounding periods. For example, within a year when dealing with months, r would be divided by 12 if the source of interest is annual based. The individual periods are then compounded using the power over all the periods eg 24 periods for 2 years – see the examples below. NOTE the r/n is an empirical adjustment required because quoted interest rates from most sources are over a year (annual). Whenever obtaining interest rates from a source we must be careful with the r/n calculation as it may need to be ignored. If for some reason the period over which interest is defined in the source list is anything other than annually this r/n calculation may require alteration..

UK Base Interest Rates source
Bank of England Base Rate

XLS File of Base Rate changes since 1664 to June 2022
Note – I took a snap shot at June 2022 for latest go to Bank of England base rate where there is(at October 2022) a constantly updated xls file.

So for example if we want to calculate interest on £100,000 over a period of 4 years and 8 months based on an interest rate of 4.0% over the base of 0.5% over differing compound periods;
Compounded annually;
A = 100,000 ( 1 + (0.045/1) )^4.67 = £122,821.10
A = £122,821.10

Compounded Monthly;
A = 100,000 ( 1 + (0.045/12) )^56 = £123,319.40
A = £123,319.40

Compounded Daily
A = 100,000 ( 1 + (0.045/365) )^1704 = 123,376.30
A = £123,376.30
Please note : simplification this calculation leap years re-calculate if important
(^ indicates to the power of)

Methodology – How to calculate the Interest rate to be used

At the Bank of England the Monetary Policy Committee (MPC) made up of appointed members set the bank of England Base rate. When they do this it normally makes the national news. For example on the 3rd of August 2023 the Monetary Policy committee set the interest rate at 5.25%. This is the interest figure I use in the above compound interest rate when I need to calculate interest to be added by the council for holding money over a given period. But because for any given period it is unlikely that interest rates will have been uniform for the entire period it is necessary to either calculate one figure for the entire period or alternatively calculate the interest on the principle for each sub period and aggregate the figure for each of the periods. Below shows a method of calculating a single average daily interest rate for any period given changes to the rates.

So for example:

Calculate the interest rate to be used for a return of developer contributions received on the 11 May 2023 and required to be returned on the 18 August 2023 in the knowledge that the interest rate on the 11 May 2023 was 4.5% but was raised by the MPC to 5% on 22 June 2023 and then raised to 5.25% on the 03 August 2023. Develop a framework that can be used to calculate an applicable interest rate that can be used in the compounding interest rate calculation.

Average interest rate over this period is calculated as 477.75/99 or 4.825757%

This figure is consistent with the reporting by the Bank of England which changes rates on individual days rather than at the end of years or months. As such we should use the Compounding on a Daily basis to calculate the compounded interest to be returned as a result of holding this money for 99 days.

Normally, for developer contributions, additional interest will be calculated over periods of years rather than mere days but the above template can be used for any given time period. Additionally and importantly it calculates the total number of days which is also required for the compound interest rate calculation.

MS Azure – SQL Azure – Reliability update 001 (38 days) – using Azure Logic App to execute a Stored Procedure

So a month ago in an non critical application I set up a trigger for a stored procedure to copy records (about 3000) from one table to another 6 times a day.

Approximately 38 days in and I have 100% reliability.

I have firewalled the required IP addresses listed by Microsoft
Azure Logic Apps list of IPs for Firewalls

I will keep running this for testing purposes to identify the reliability and report back

MS Azure – TSQL a simple example using a Common Table Expression (CTE)

A common table expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and exists only for the duration of the query.

I’m finding them useful as you can use them within a view to contain all the references required for that particular view. This I feel makes things more maintainable.

An example of a simple common table expression (CTE) in T-SQL:

WITH CTE_EmployeeSalaries AS
(
    SELECT EmployeeID, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT *
FROM CTE_EmployeeSalaries;

This CTE defines a virtual table named CTE_EmployeeSalaries that contains all employees with a salary greater than 50,000. The CTE is then used to select all rows from this virtual table.

Common table expressions (CTEs) have several advantages:

1.Improved readability: can make complex queries easier to read and understand by breaking them up into smaller, more manageable pieces.
2.Improved maintainability: Because CTEs are self-contained, they can be easily modified or replaced without affecting the rest of the query.
3.Improved performance: In some cases can improve query performance by reducing the need for subqueries.
4.Recursive queries: CTEs can be used to perform recursive queries, which are useful for querying hierarchical data such as an organizational chart or a bill of materials.
5.Temporary results: can be used to store intermediate results that are used multiple times within a single query. This can be more efficient than repeatedly calculating the same intermediate results.
6.Better alternative to view: CTEs can be used as an alternative to views, which can be difficult to modify and are not always optimized for performance.
7.Reusable: CTEs can be defined once and used multiple times within a single query or in multiple queries within the same session.

MS Azure TSQL writing comments

In TSQL, comments can be added to code in two ways:

1.Single-line comments: These start with two hyphens (–) and continue until the end of the line. For example:

-- This is a single-line comment

2.Multi-line comments: These start with a forward slash and an asterisk (/) and continue until the closing asterisk and forward slash (/). For example:

/*
This is a multi-line comment. It can span
multiple lines and is often used to provide
detailed explanations or to comment out large
blocks of code.
*/

When writing comments, it’s important to keep them clear and concise, and to use them to explain why something is being done rather than how it is being done. This helps to make your code more readable and easier to understand for other developers who may be reading it.