Implementing the yield and price functions in Excel based on Java
Returns the yield on a bond that pays periodic interest. The function YIELD is used to calculate bond yields.
To put it bluntly, I may be the first person on the entire Internet to post this resource. Because of this requirement, I searched the entire Internet for a month but could not find the Java implementation of the corresponding code. So I stamped my teeth and bit my foot. I implemented a version myself and successfully tested it online in our company. The accuracy is undoubtedly reliable. Alas~
The following description comes from Microsoft Support
There are many restrictions on input parameters, and the company does not allow the code to be copied. I will not write it in the article. For specific restrictions, please check the official support website: Yield function description in Excel
Grammar
YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
Important: Dates should be entered using the DATE function or as the result of another formula or function. For example, use the function DATE(2008,5,23) to enter May 23, 2008. The problem occurs if the date is entered as text.
The YIELD function syntax has the following parameters:
-
Settlement Required. The settlement date of a security. The settlement date of the securities is the date after the issuance date when the securities are sold to the purchaser.
-
Maturity Required. The maturity date of a security. The expiry date is the date when a security expires.
-
Rate required. The annual coupon rate of a security.
-
Pr Required. The price of a marketable security (based on a par value of $100).
-
Redemption Required. The liquidation value of a security with a face value of $100.
-
Frequency Required. Number of annual interest payments. If you pay annually, frequency = 1; if you pay semiannually, frequency = 2; if you pay quarterly, frequency = 4. Optional. The type of day count basis to use.
-
Basis Daily count basis
Calculation formula part
The first thing to note: If there is only one or no interest payment period before the settlement date, the calculation formula of function yield is:
If there is more than one interest payment period then function YIELD is calculated through 100 iterations. Based on the formula given in the function PRICE, the calculation results are continuously corrected using Newton’s iteration method. In this way, the yield will continue to change until the estimated price calculated based on the given yield is close to the actual price.
In other words, if you want to solve yield, you must first implement the prices function:
PRICE (settlement, maturity, rate, yld, redemption, frequency, [basis])
The function calculates the price per $100 par value of a security that pays periodic interest.
The specific parameter description is the same as yield. You can also find the description of this function on the Microsoft support page. There are several particularly confusing intermediate variables:
N is the number of interest payments between the settlement date and the settlement date),
DSC The number of days between the settlement date and the next interest payment date.
E The number of days in the interest payment period in which the settlement date falls.
A The number of days to the settlement date in the current interest payment period.
What do these four numbers mean? This is the most painful thing in my development process. After my unremitting efforts and almost exhaustive attempts, I found that these four parameters correspond to the other four functions in Excel. , if you need specific ones, you can comment in the comment area, and I will send them to you one by one (again, the company does not allow documents to be brought out, and it is really inconvenient to copy many things).
First is the price function
public class YtmUtil {<!-- -->
private static final int YEARS = 360;
private static final int MONTHS = 12;
public static double prices(LocalDate settlement, LocalDate maturity, double rate, double yld, double redemption, int frequency) {<!-- -->
int interval = MONTHS/frequency;
int E = YEARS/frequency;
double N = 0;
while (settlement.isBefore(maturity)) {<!-- -->
settlement = settlement.plusMonths(interval);
N + + ;
}
int settlementMonth = settlement.getMonthValue()%interval;
int maturityMonth = maturity.getMonthValue()%interval;
int A = (settlementMonth - maturityMonth)*30 + (settlement.getDayOfMonth() - maturity.getDayOfMonth());
if (A < 0) {<!-- -->
A + = YEARS/frequency;
}
int DSC = E - A;
if (N == 1) {<!-- -->
return prices(settlement, maturity, rate, yld, redemption, DSC, E, A, frequency);
}
return prices(settlement, maturity, rate, yld, redemption, DSC, E, A, N, frequency);
}
static double prices(LocalDate settlement, LocalDate maturity, double rate, double yld, double redemption,
double DSC, double E, double A, double N, long frequency) {<!-- -->
double g1 = redemption/(Math.pow(1 + yld/frequency, N - 1 + (DSC/E)) );
double g2 = 0;
for (int k = 1; k <= N; k + + ) {<!-- -->
g2 + = (100*rate/frequency)/Math.pow(1 + yld/frequency, k - 1 + (DSC/E));
}
double g3 = (100*(rate/frequency)*(A/E));
return g1 + g2 - g3;
}
static double prices(LocalDate settlement, LocalDate maturity, double rate, double yld, double redemption,
double DSC, double E, double A, long frequency) {<!-- -->
double DSR = E - A;
double T1 = 100*(rate/frequency) + redemption;
double T2 = (yld/frequency)*(DSR/E) + 1;
double T3 = 100*(rate/frequency)*(A/E);
return T1/T2 - T3;
}
}
What is written on the Microsoft support website is that the yield function is calculated through the Newton iteration method. I think this is official bragging. Everyone knows that the Newton iteration method requires derivation, and the results derived from such a complex formula are also very complicated. , I don’t know if I’m too bad at math. In short, I used the Newton iteration method to solve the problem and the accuracy was seriously lost, and the results were all 0.
Here I am using binary search to solve the problem
public static double yield(LocalDate settlement, LocalDate maturity, double rate, double pr, double redemption, int frequency) {<!-- -->
double left = 0, right = 1, yld = 0.5;
double proPr = prices(settlement, maturity, rate, yld, redemption, frequency);
while (Math.abs(pr - proPr) > precision){<!-- -->
if (proPr > pr) {<!-- -->
right = yld;
}else {<!-- -->
left = yld;
}
yld = (left + right)/2;
proPr = prices(settlement, maturity, rate, yld, redemption, frequency);
}
return yld;
}
The above code was written by me from memory, and there are shortcomings. For example, the value of yield may be greater than 1. In this case, just improve the dichotomy method according to your needs.
It should be the first release on the entire network, thank you! ! !