C#编写自定义Excel公式计算非均匀现金流的NPV与IRR

来源：互联网发布：mysql error code 1022 编辑：程序博客网时间：2024/05/17 14:27

1、用Visual Studio新建一个类库，命名为CashFlowTools

2、引用必要的类库：

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Runtime.InteropServices;using Microsoft.Win32;using Microsoft.Office.Interop.Excel;

其中最后一个需要在“引用”中添加一个COM组件“Microsoft.Office.Interop.Excel”

3、添加COM相关属性

[Guid("DDE8AA59-9860-44B3-B1AF-923ABB4A8EEE")] [ClassInterface(ClassInterfaceType.AutoDual)] [ComVisible(true)]

注意要生成一个GUID：

这里我使用的LinqPad，一个轻量级的IDE，支持C#、VB.NET、Linq、F#、SQL语句，非常适合用来测试少量代码。

4、添加COM的注册和反注册函数，这两个函数代码是固定的。添加后的程序结构如下

namespace CashFlowTools{    [Guid("DDE8AA59-9860-44B3-B1AF-923ABB4A8EEE")]    [ClassInterface(ClassInterfaceType.AutoDual)]    [ComVisible(true)]     public class CashFlowTools    {        #region COM related        [ComRegisterFunction]        public static void RegisterFunction(Type type)        {            Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"));            var key = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true);            key.SetValue("", Environment.SystemDirectory + @"\mscoree.dll", RegistryValueKind.String);        }        [ComUnregisterFunction]        public static void UnregisterFunction(Type type)        {            Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), false);        }        private static string GetSubKeyName(Type type, string subKeyName)        {            var s = new System.Text.StringBuilder();            s.Append(@"CLSID\{");            s.Append(type.GUID.ToString().ToUpper());            s.Append(@"}\");            s.Append(subKeyName);            return s.ToString();        }        #endregion        // 以下为自定义Excel函数        // ...    }}

5、编写自定义函数

        /// <summary>        /// Calculate net present value        /// </summary>        /// <param name="timeRange">Excel Range stores the time of the cash flows</param>        /// <param name="cashFlowRange">Excel Range stores the amount of cash flows</param>        /// <param name="discountRate">The discount rate</param>        /// <param name="CompoundFrequency">the frequency measure in unit of time, for example, if the times of cash        /// flow are in days but we want to measure NPV in year bases, CompoundFrequency should be set as 365.</param>        /// <returns>The net present value</returns>        public double ExtNPV(Range timeRange, Range cashFlowRange, double discountRate, double CompoundFrequency = 1)        {            if (timeRange.Columns.Count != 1 || cashFlowRange.Columns.Count != 1                || timeRange.Rows.Count != cashFlowRange.Rows.Count)                return Double.NaN;            int length = timeRange.Rows.Count;            double[] timeArray = new double[length];            double[] cashFlowArray = new double[length];            for (int i = 0; i < length; i++)            {                timeArray[i] = Convert.ToDouble((timeRange.Cells[i+1,1] as Range).Value2);                cashFlowArray[i] = Convert.ToDouble((cashFlowRange.Cells[i + 1, 1] as Range).Value2);             }            for (int i = 1; i < length; i++)                timeArray[i] = (timeArray[i] - timeArray[0]) / CompoundFrequency;            timeArray[0] = 0;            return NetPresentValue(timeArray, cashFlowArray, discountRate);        }        /// <summary>        /// Calculate the internal rate of return using Newton's method.        /// </summary>        /// <param name="timeRange">Excel Range stores the time of the cash flows</param>        /// <param name="cashFlowRange">Excel Range stores the amount of cash flows</param>        /// <param name="guess">the initial estimate for the Newton's method </param>        /// <param name="CompoundFrequency">the frequency measure in unit of time, for example, if the times of cash        /// flow are in days but we want to measure NPV in year bases, CompoundFrequency should be set as 365.</param>        /// <param name="eps">The resolution control</param>        /// <returns>the internal rate of return, not annualized</returns>        public double ExtIRR(Range timeRange, Range cashFlowRange, double guess = 0, double CompoundFrequency = 1, double eps = 1e-8)        {            if (timeRange.Columns.Count != 1 || cashFlowRange.Columns.Count != 1                || timeRange.Rows.Count != cashFlowRange.Rows.Count)                return Double.NaN;            int length = timeRange.Rows.Count;            double[] timeArray = new double[length];            double[] cashFlowArray = new double[length];            for (int i = 0; i < length; i++)            {                timeArray[i] = Convert.ToDouble((timeRange.Cells[i + 1, 1] as Range).Value2);                cashFlowArray[i] = Convert.ToDouble((cashFlowRange.Cells[i + 1, 1] as Range).Value2);            }            for (int i = 1; i < length; i++)                timeArray[i] = (timeArray[i] - timeArray[0]) / CompoundFrequency;            timeArray[0] = 0;            double x1 = guess, x2 = guess, x3 = 10000;            do            {                x1 = x2;                x2 = x1 - NetPresentValue(timeArray, cashFlowArray, x1) / DerivativeNPV(timeArray, cashFlowArray, x1);                if (Math.Abs(x1 - x2) < x3)                    x3 = Math.Abs(x1 - x2);                else                    return Double.NaN;            } while (x3 > eps);            return x2;        }        private double NetPresentValue(double[] time, double[] cashFlow, double discountRate)        {            double npv = 0;            for (int i = 0; i < time.Length; i++)                npv += cashFlow[i] / Math.Pow(1 + discountRate, time[i]);            return npv;        }        private double DerivativeNPV(double[] time, double[] cashFlow, double discountRate)        {            double dnpv = 0;            for (int i = 1; i < time.Length; i++)                dnpv -= time[i] * cashFlow[i] / Math.Pow(1 + discountRate, time[i] + 1);            return dnpv;        }

6、修改项目属性，取消COM注册：

如果需要调试程序，在“调试”下的“启用外部程序”中添加Excel的路径命令行

7、注册加载DLL

我们需要使用程序regasm来注册我们的DLL。

首先以管理员身份运行CMD，使用命令

cd /d %windir%\Microsoft.NET\Framework\v4.0.30319

切换到regasm所在目录，然后运行以下命令

C:\Windows\Microsoft.NET\Framework\v4.0.30319>RegAsm /codebase "D:\Documents\Visual Studio 2012\Projects\CashFlowTools\CashFlowTools\bin\Debug\CashFlowTools.dll"Microsoft .NET Framework 程序集注册实用工具版本 4.0.30319.18408(适用于 Microsoft .NET Framework 版本 4.0.30319.18408)版权所有 (C) Microsoft Corporation。保留所有权利。RegAsm : warning RA0000 : 使用 /codebase 注册未签名的程序集可能会导致程序集妨碍可能在同一台计算机上安装的其他应用程序。/codebase 开关旨在仅用于已签名的程序集。请为您的程序集提供一个强名称并重新注册它。成功注册了类型

显示关于签名的警告现在可以无视，但如果公开发布正式版则必须签名。

8、在Excel中加载该公式

首先，在Excel中右键功能区，选择“自定义功能区”，加入“开发工具”选项卡（如果已有则可忽略此步骤）。

在“开发工具”点击“加载项”按钮，弹出如下对话框，选择刚刚编写的类库

9、注销自定义函数

在CMD中反注册该DLL：

C:\Windows\Microsoft.NET\Framework\v4.0.30319>RegAsm /unregister "D:\Documents\Visual Studio 2012\Projects\CashFlowTools\CashFlowTools\bin\Debug\CashFlowTools.dll"Microsoft .NET Framework 程序集注册实用工具版本 4.0.30319.18408(适用于 Microsoft .NET Framework 版本 4.0.30319.18408)版权所有 (C) Microsoft Corporation。保留所有权利。成功注销了类型

0 0