Excel实现Excel Yield函数

Excel's add-in named "Analysis ToolPak" provides "Yield" function for calculation of yield on security that pays periodic interest.

名为“Analysis ToolPak”的Excel加载项提供“收益”功能,用于计算定期支付利息的证券收益率。

Function works well and returns proper data.

功能运行良好并返回正确的数据。

My understanding is function base on kind of iteration it is not so easy implement it in my code.

我的理解是基于迭代类型的功能,在我的代码中实现它并不容易。

My question is anybody know/seen implementation in C# (eventually other languages) and can share? Or (maybe) some tips how to implement it? Than I can share :)

我的问题是任何人都知道/看到C#(最终其他语言)的实现并且可以共享?或者(也许)一些提示如何实现它?比我可以分享:)

EDIT:

Thanks all posting me "formula" but this is not fully useful to me.
Please note that MS' formula finds application in 1 case only:
"when there is one coupon period or less until redemption",

谢谢所有发布我的“公式”,但这对我来说并不完全有用。请注意,MS'公式仅在1个案例中找到申请:“当有一个或更少的优惠券期限到兑换时”,

otherwise:

"(...) Yield is calculated through a hundred iterations."
and there is no exact formula for that case

“(...)收益率通过一百次迭代计算。”这种情况没有确切的公式

I can read equations and implement them (hopefully), but my question was if somebody has or saw already implemented function in programming language.

我可以阅读方程式并实现它们(希望如此),但我的问题是,如果某人已经或已经看到已经在编程语言中实现了函数。

I'm not lazy but I dont like break down open doors...

我不懒,但我不喜欢打开门......

5 个解决方案

#1

The whole formula is here:

整个公式如下:

http://office.microsoft.com/en-us/excel/HP052093451033.aspx

First match on Google for 'excel yield'.

谷歌首次在'excel yield'上比赛。

#2

Assuming a function which is passed all the variables required for the formula as explained on

假设一个函数传递了公式所需的所有变量,如上所述

http://office.microsoft.com/en-us/excel/HP052093451033.aspx

then

return (((redemption/100 + rate/frequency) - ( par/100 + ( A/E * rate/frequency ))) / (par/100+( A/E * rate/frequency ))) * ((frequency*E)/DSR);

#3

First you need an implementation of the PRICE function. YIELD then uses a Newton Solver to solve the PRICE function for the correct yield and a given target price. Code for price function and code for the Newton solver can be found in this similar post: java implementation of excel price,yield functions

首先,您需要实现PRICE功能。然后YIELD使用牛顿求解器来求解PRICE函数以获得正确的收益率和给定的目标价格。牛顿求解器的价格函数代码和代码可以在这个类似的帖子中找到:excel price的java实现,yield函数

#4

Here is the formula if you don't want to browse through web pages.

如果您不想浏览网页,可以使用以下公式。

I tried to post the image but I am a new user and it didn't allow me.

我试图发布图像,但我是一个新用户,它不允许我。

Graphic showing formula.

图形显示公式。

#5

using Excel = Microsoft.Office.Interop.Excel; 

 Excel.Application xl = new Excel.Application();
        Excel.WorksheetFunction wsf = xl.WorksheetFunction;
        //static settlementDate
        var settlementDate = xl.Evaluate("=DATEVALUE(" + '"' + "9/4/2013" + '"' + ")");
        //static maturityDate
        var maturityDate = xl.Evaluate("=DATEVALUE("+ '"' +"12/5/2014"+ '"' +")");
        var rate = "0.05250";
        var priceLevel = "1.04800";
        //assuming that redemption is 100, frequency is Quarterly and basis is Actual/365
        var resultInPercentage = xl.Evaluate("=YIELD(" + settlementDate + "," +         maturityDate + ",.05250,(1.04800*100),100,2,3)") * 100;