Software Search, Categories and Specials
Ozgrid, Experts in Microsoft Excel Spreadsheets
Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com

Active DataXL - Download

 

AnalyserXL - Download

 

DownloaderXL - Download

 

Smart VBA - Download

 

TraderXL - Download

Financial Calculation Library

Microsoft Excel - From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

 

Performs Analytical Calculations, Generates Cash Flows for Many Basic Financial Instruments

Financial Calculation Library

Financial calculation library can perform a wide variety of calculations and generate cash flows for financial instruments such as amortizations, loans, annuities, CDs amd IRAs.

Financial Calculation Library Features

  • Financial instruments represented
    • Mortgages
    • Personal loans
    • Certificates of Deposit
    • Amortization Schedules
    • Deposit Accounts
    • Annuities
    • Annuities receiving contributions
    • Annuities generating income
    • IRAs
    • Future Value/Present Value of Funds
  • Pure ActiveX Objects with no user interface
  • "Both" Threaded - can be used in single-threaded and multi-threaded applications
  • Compatible with
    • Visual Basic (VB6)
    • Active Server Pages (ASP)
    • Visual Interdev
    • Visual C++ (VC++ 6)
    • Windows Scripting Host (VBScript and JScript)
    • Visual Basic for Applications (VBA)
    • Microsoft Office (Word, Excel, Access) with VBA
    • Delphi
  • Full cash-flow tables
  • Cash-flow tables as collections (supports "For Each ... Next")
  • Solve for any value
  • No mathematical knowledge required
  • Small memory footprint - does not require MFC, MSVCRT, ATL, or the VB6 Runtime

AtFinCalc Financial Calculation Library Technical Details

AtFinCalc.dll Help

Contents

Installing your license key

You can install the license key you received with your purchase by using the Atereon License Manager. The license manager can be found on the "Start Menu" under "Programs | Atereon" as "Register Atereon Software".

In the license manager, select "AtFinCalc" as your application and enter your license key.

Using the Library back to contents

How to use the AtFinCalc.dll library.

Visual Basic

Step 1: Add a reference to the AtFinCalc.dll

  1. Open your project
  2. On the menu, select Project | References
  3. Put a check next to "Atereon FinCalc 1.0 Library"
  4. Click "Ok"

Step 2: Create an Object

AtFinCalc Objects can now be created using a Dim statement, either referencing the library or not.

Dim mLoan as New Loan
dim mAnn1 as New GrowingAnnuity
dim mAnn2 as New PayingAnnuity
dim mDep as New Deposit
dim mFlowItem as CashFlow
dim mFlows as CashFlows

or

dim mLoan as new AtFinCalc.Loan
dim mAnn1 as new AtFinCalc.GrowingAnnuity
dim mAnn2 as new AtFinCalc.PayingAnnuity
dim mDep as new AtFinCalc.Deposit
dim mFlowItem as AtFinCalc.CashFlow
dim mFlows as AtFinCalc.CashFlows

Visual C++ back to contents

The AtFinCalc.dll objects can be added to a Visual C++ project using the #import directive in a header file. The AtFinCalc objects can be created using the compiler generated SmartPointers and their "CreateInstance" methods.

The #import directive creates a header file "AtFinCalc.tlh" that can be added to your project to get Class View references for the AtFinCalc objects.

Don't forget to call CoInitialize(NULL) to set up COM.

All "optional" parameters should be passed in as 0 or 0.0 to use their default values.

#include "stdafx.h"
#include <comdef.h>
#import "c:\libraries\AtFinCalc\AtFinCalc.dll" no_namespace

int main(int argc, char* argv[])
{
  CoInitialize(NULL);

  ILoanPtr m_loan;
  m_loan.CreateInstance("AtFinCalc.Loan");
  m_loan->Amount = 1000.0;
  m_loan->Periods = 24;
  m_loan->Rate = 0.10 / 12.0;
  printf("%.2f\n", m_loan->CalcPayment(0, 0, 0));

  CoUninitialize();
  return 0;
}

ASP - Active Server Pages

In ASP use the Server.CreateObject function to create AtFinCalc.dll objects.

dim mLoan
set mLoan = Server.CreateObject("AtFinCalc.Loan")

VB Script

In VB Script use the CreateObject function to create AtFinCalc.dll objects.

dim mLoan
set mLoan = CreateObject("AtFinCalc.Loan")

Redistributing the library as part of your product  back to contents

Note:
Redistributing the AtFinCalc library requires a client licenses for each target workstation, a single-server license for each network server (including, but not limited to web servers), or a redistribution agreement for the product with Atereon, Inc.

Using Windows Installer

The AtFinCalc distribution includes a Windows Installer Merge Module (MSM) file that can be included in your own project. This is the preferred method of redistributing the library.

  1. AtFinCalc.MSM

Other installation toolkits back to contents

If you want to create an install manually, you need to include the following:

  1. AtFinCalc.dll
  2. AtLicense.dll

Both files need to be registered. The standard location for these files is "Program Files\Common Files\Atereon".

In order to install your license you will need to use the Atereon License manager or the code below. In the code below, replace the license key with the license key you received with your purchase. If a license is not installed the evaluation license will expire after 30 days, and your application will no longer function.

dim atfincalc as new AtFinCalcLib
call atfincalc.SetLicense("1234-5678-1234-5678")


CashFlows Object back to contents

The CashFlows Object is a Collection of CashFlow Objects. The contained CashFlow Objects can be referenced by index (flows(i))or using the iterator methods (For Each flow in mFlows).

CashFlow Object

This object contains a single entry in a cash flow table (the CashFlows Collection Object). Each item in a CashFlows Collection is a CashFlow object.

Not all properties have values in every cash flow. For example, a CashFlows collection created by a Loan Object has no Contribution or Distribution properties.

Properties

Balance The remaining principle of a financial instrument at the beginning of the period.
Contribution The amount contributed to an annuity in this period.
Distribution The amount paid out of an annuity in this period.
Interest The amount of interest paid on a loan payment, or added to a Deposit or GrowingAnnuity Object in this period.
Payment The payment made this period.
Principle The amount of principle paid on a loan payment in this period

Methods back to contents

None

Example

Problem:

What does the cash flow table for a mortgage look like?

Dim mort as New Loan
dim flows as CashFlows
dim i as long
mort.Amount = 100000
mort.Periods = 360 ' 30 Years
mort.Rate = .0725/12 ' 7.25%
set flows = mort.CalcCashFlows
for i = 0 to flows.Count - 1
  ' Do processing
  principle = flows(i).Balance  ' Starting Principle
  interest = flows(i).Interest  ' Interest Paid
  payment = flows(i).Payment  ' Payment Amount
  ' flows(i).Payment  = flows(i).Interest + flows(i).Principle
next i

' Or do it this way
dim flow as CashFlow
for each flow in flows
  ' Do processing
  principle = flow.Balance  ' Starting Principle
  interest = flow.Interest  ' Interest Paid
  payment = flow.Payment    ' Payment Amount
  ' flow.Payment  = flow.Interest + flow.Principle
next flow


Deposit Object back to contents

This object is used for calculations on Deposit accounts -- accounts that have an initial deposit and receives interest at a fixed rate over a period of time. This includes financial instruments such as savings accounts and certificates of deposit.

You can combine the calculations of a Deposit Object with the calculations of a GrowingAnnuity Object to perform calculations for an annuity that has an initial value and is also receiving regular contributions. See the GrowingAnnuity Object example for an example.

Properties

FutureValue The value of the deposit (PresentValue) after Periods of Rate interest.
Periods The number of periods of interest compounding. If the deposit will be for 2 years with interest compounded monthly then:
Periods = 12 * 2 = 24.
If the interest will be compounded continuously, use a large number of periods (such as 3000) per year.
PresentValue The amount of the deposit.
Rate The interest rate on the deposit per period. If the rate is 5%, and it its compounded daily, then:
Rate = 0.05 / 360 = .0001389.
Note: When the rate is calculated, its value is rounded to 6 decimal places.
Yield The calculated APY of Rate over Periods. Periods should be for 1 year to make this A (annual) P (percentage) Y (yield).

Methods

For each function, you can either set the properties in advance or pass them in as parameters.

CalcAPY Calculate the APY of Rate at Periods/year. The result of this calculation is discarded. Requesting the Yield property is the same as calling this method.
CalcFutureValue Calculate the the future value of the deposit using PresentValue, Rate and Periods.
CalcPeriods Calculate the Periods needed for PresentValue to grow to FutureValue at Rate.
CalcPresentValue Calculate the PresentValue based using FutureValue, Rate and Periods.
CalcRate Calculate the Rate required for PresentValue to reach FutureValue in Periods.

Example back to contents

Problem:

What will a $1000 18 month CD at 6% be worth when it matures?

dim dep as new Deposit
dim value as Double
dep.PresentValue = 1000
dep.Periods = 18
dep.Rate = .06/12
value = dep.CalcFutureValue

And what APY is that?

dim apy as Double
dep.Periods = 12
apy = dep.Yield


GrowingAnnuity Object back to contents

This object is used for calculations on annuities that are paying no distributions, but are receiving regular contributions.

You can combine the calculations of a GrowingAnnuity Object with the calculations of a Deposit Object to calculate values for an annuity that already has principle. Use the Deposit Object for the current principle and the Growing Annuity Object for the contributions. See the example below.

Properties

Contribution The amount of each contribution, one per period. If there are 12 contributions per year, and the total annual contribution is $1000, then:
Contribution = $1000 / 12 = $83.33.
EndingValue The principle in the annuity after all contributions have been made.
Periods The number of periods over which contributions will be made. If there are 15 years before retirement, with monthly contributions, then:
Periods = 12 * 15 = 180.
Rate The interest rate of the annuity per period. If the rate is 10%, and distributions are made monthly, then:
Rate = .10 / 12 = .0083333.
Note: When the rate is calculated, its value is rounded to 6 decimal places.

Methods

Except for CalcCashFlows Function, you can either set the Properties in advance or pass them in as parameters to the function call.

CalcCashFlows Create a CashFlows Collection for the annuity. The calculation uses the current values of Contribution, Rate and EndingValue.
CalcContribution Calculate the Contribution required for the current the number of Periods, Rate and EndingValue.
CalcEndingValue Calculate the final principle in the annuity after Periods, using the current Rate and Contribution for each period.
CalcPeriods Calculate the Periods required to reach EndingValue using the current values of Contribution and Rate.
CalcRate Calculate the Rate of return required for the annuity reach EndingValue using the current values of Contribution and Periods.

Example back to contents

Problem:

My annuity currently has $10000 in it. What will its value be in 10 years with a quarterly contribution of $2500 at a 9% rate of return.

dim growAn as New GrowingAnnuity
dim dep as New Deposit
dim value as Double
growAn.Periods = 10 * 4
growAn.Rate = .09 / 4
growAn.Contribution = 2500
dep.Periods = 10 * 4
dep.Rate = .09 / 4
dep.PresentValue = 10000
value = dep.CalcFutureValue() + growAn.CalcEndingValue()


Loan Object back to contents

This object is used for calculations on loans (amortization schedules) with fixed interest rates over a fixed period of time. For example a fixed-rate mortgage, an adjustable rate mortgage during its initial fixed-rate period, a balloon mortgage before the balloon payment is due, or an installment loan.

If you calculate a Payment amount, the amount of the final payment will probably be different from every other Payment, this is reflected in the CashFlows Collection generated. If you calculate another parameter using a Payment amount, such as Amount or Rate, the calculation assumes that all payments (including the final payment) will be equal. The resulting side-effect is that an Amount calculated from a Payment can be slightly different from an identical Payment calculated from another Amount.

For example, the calculated Payment for a $1000.00 loan at 10% over 12 periods, is $87.92 per period. The same Payment is also calculated for a $1000.10 loan and a $1000.05 loan. The Amount calculated from a $87.92 payment is always $1000.05.

Properties

Amount The original principle amount of the loan. For example, if you buy a $100,000 home, with 20% down, then the Amount of the loan is $80,000.
Payment The payment to be made each period. If the payments are monthly, then each payment is 1/12 of the annual payment.
Periods The number of periods before the loan is fully paid off. For a 15 year mortgage with monthly payments, this value would be 180 payments (15 * 12 = 180).
Rate The interest rate of the loan (per period). If the rate is 10%, and payments are made monthly, then:
Rate = .10 / 12 = .0083333.
Note: When the rate is calculated, its value is rounded to 6 decimal places.

Methods back to contents

Except for CalcCashFlows Function, you can either set the Properties in advance or pass them as function call parameters.

CalcAmount Calculate the loan amount using Payment, Rate and Periods. This method assumes that all payments including the final one are equal.
CalcCashFlows Create a CashFlows Collection for the amortization schedule. The schedule is based on the current values of Payment, Rate and Amount.
CalcPayment Calculate the payment required for a loan of Amount, Rate and Periods.
CalcPeriods Calculate the Periods until the loan is paid off, using Amount, Rate and Payment.
CalcRate Calculate the interest Rate of the loan if Amount is paid off over Periods with Payment.

Example

Problem:What payment is required for a $100,000 mortgage over 15 years at 7.25%.

Dim mort as New Loan
dim payment as Double
dim orig as Double
orig = 100000
mort.Amount = orig
mort.Periods = 15 * 12
mort.Rate = .0725 / 12
payment = mort.CalcPayment

If the payments are all equal, what would the original Amount be?

dim amt as Double
' Now work backwardsamt = mort.CalcAmount
' amt &ne; orig

Paying Annuity Object back to contents

This object is used for calculations on Annuities that are paying distributions. The PayingAnnuity Object can be used to calculate the principle required for an annuity to last a given period, the rate of return required to guarantee an payments, the periods an annuity will last or the payment available from an annuity.

You can combine the calculations of PayingAnnuity Object with the calculations of a GrowingAnnuity Object to determine the contributions required to create an annuity that will last for a given period. See the example below.

Properties

Distribution The amount of each distribution, one per period. If there are 12 distributions per year, and the total annual distribution is $1000, then:

Distribution = $1000 / 12 = $83.33.

Periods The number of periods before the annuity has zero principle. If the annuity must last 20 years, with monthly distributions, then:

Periods = 12 * 20 = 240.

If the annuity will be perpetual, set periods to be a very large number (such as 100 years).
Rate The interest rate of the annuity per period. If the rate is 10%, and distributions are made monthly, then:

Rate = .10 / 12 = .0083333.

Note: When the Rate is calculated, its value is rounded to 6 decimal places.
Start ingValue The principle in the annuity before any distributions are made. The ending value of the annuity is $0.

Methods back to contents

Except for the CalcCashFlows Function, you can either set the properties in advance or pass them as parameters. to the function calls.

CalcCashFlows Create a CashFlows collection for the annuity. This calculation uses the current values of Distribution, Rate and Staring value.
Note: There will be one item for each Period, so if periods is very large, the collection will also be very large.
CalcDistribution Calculate the maximum Distribution available from the annuity it it must last the number of Periods using the current Rate and Start ingValue.
CalcPeriods Calculate the number of Periods until the annuity has zero value using Start ingValue, Distribution and Rate.
CalcRate Calculate the Rate of return required for the annuity to last Periods using Start ingValue and Distribution.
CalcStart ingValue Calculate the initial principle required for the annuity to last Periods using Rate and Distribution.

Example back to contents

Problem:

What initial principle will be required for a 20 year annuity at 10% that has monthly payments of $1000?

Dim payAn as New PayingAnnuity
dim principleRequired as Double
payAn.Rate = 0.10 / 12
payAn.Distribution = 1000
payAn.Periods = 20 * 12
principleRequired = payAn.CalcStart ingValue

How much will I need to contribute each month if I have 15 years before retirement?

Dim growAn as New GrowingAnnuity
dim monthlyDeposit as Double
growAn.EndingValue = principleRequired
growAn.Periods = 15 * 12
growAn.Rate = 0.10 / 12
monthlyDeposit = growAn.CalcContribution

AtFinCalc Financial Calculation Library Secure Payment Options

Order Type:

Special ! Free Choice of Complete Excel Training Course OR OzGrid Add-ins Collection on all purchases totaling over $70.00. ALL purchases totaling over $149.00 gets you BOTH!  Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Download Demo of AtFinCalc Financial Calculation Library!

See Also: Financial Software Index Financial Calculators Index & Excel Add-ins Index & Excel Templates Index or, All Software



Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates