EMI Calculation Explained – Excel

EMI Calculation is a rather complex math with the denominator being the integration of a series (or something of that sort). It is fairly complex to take a pen and paper and calculate. Well, that is if you do not have access to Excel or a similar spreadsheet. In a spreadsheet, it is a very straight-forward calculation to arrive at an EMI.

In this article am explaining how to calculate EMI and amortization schedule of your loan using MS Excel. Also, you can see by changing the EMI how your tenure changes. I’ve attached a sample sheet here with the calculation.

You can download it here: EMI Explained

This is the EMI formula in Excel
=PMT(D4/12,D6,-D3)
Where
D4 has the per annum interest
D6 has the tenure in months
D3 has the loan amount.

So what if your loan is quarterly diminishing (or compounding, as they say) even though you are paying monthly. In that case the formula changes a bit.
=PMT(D4/4,D6,-D3)/3
Where
D6 has the tenure in quarters (i.e. # of years multiplied by 4)
D3 and D4 remain unchanged.

Note the interest changed from D4/12 to D4/4 and tenure from Years * 12 to Years * 4. Also, there is a division by 3 at the end to make it monthly. Similarly you can extend it to any compounding interval.

With slight changes you can use this to calculate the return of a SIP, assuming an average return over the tenure you plan to invest.

The attached sheet has more than just the EMI formula. It’ll generate the amortization schedule for you, given a tenure so that you can play with the EMI amount to reach at a preferred tenure.

Update: Came to know later that Microsoft has similar template for amortization schedule and loan analysis. You can get it here.

Add a Sent Items Rule in Outlook 2007

This is something I haven’t been able to figure out for a while, and had resorted to manually running a rule once in a while on my sent items folder. With a lot of googling and going through various websites, finally figured out a way to separate my official and personal mails into two separate folders.

I’ll list them out here as a problem statement and the steps through which I could do the same. Applying some logic to the order in which the ruled are and the settings, this can be extended for all Sent Items rules.

Objective: Move unofficial mails to a separate folder and maintain official mails in the Sent Items folder.

Step 01: Disable Sent Items Auto Save
Go to Tools > Options > E-mail Options
Uncheck “Save copies of messages in Sent Items folder”

Step 02: Set a rule to move official mails to Sent Items
Start with a blank rule and select a sent items rule as below
Sent Items Rule

Set the following:
With mycompany.com in the recipient’s address
Move copy to folder Sent Items
Be sure to check “stop processing more rules
Stop Processing More Rules

Step 03: Set a rule to move ALL sent mails to Unofficial folder
Start with a blank sent items rule as above
Set the following:
Sent from account Microsoft Exchange Server
Move copy to folder Unofficial
Through Specified Account

Since you have stopped further rule processing after the rule set under Step 02, such mails (i.e. official ones in my example) will not hit Step 03 rule. For this to work, make sure Step 03 rule is below the Step 02 rule.

Now, all mails except the ones to mycompany.com will be delivered to Unofficial folder whereas the ones to mycompany.com will remain in Sent Items.

Move Data Files – SQL Server

[adsense slot=”5402167102″ width=”468″]

One of those clean-up tasks we come across in development severs is to move database mdf and ldf files to a new location. SQL Server isn’t as GUI-friendly yet to allow one to just change the paths on database properties. I have tried this out in SQL Server 2008, but the method must essentially be the same in 2005 and 2000 versions also.

What we have to do is this.
– Detach the Database
– Move the files
– Attach the database with the new location

Detach

Instead of “use master”, you can just set the query window drop-down to any DB other than DB_To_Move

Now, you need to move the files from its current location to wherever you need it to be (say, d:\data\)

Attach

Again, ideally it is just the sp_attach_db line that you would need to execute. Make sure the file security permissions are set aptly at the target location.

However, things get a bit tricky if you want to move system db files to a new location (we don’t usually findourselves doing this). Please refer this for further reference on that.

Dynamic Casting in C#

Dynamic Casting? Well… almost. In fact, we are dynamically converting and not casting. The problem that remains is that the resultant object does doesn’t “know” at design time what it is capable of (a.k.a. not aware of the methods it has). However, this should not be a problem if there is an interface that is common to the superset of things we want to cast to.

To state an example, let us say there is an interface and 3 classes as below.

Now, we can try accessing these objects through converting at runtime. Note that the examples here might look trivial, but the application for this is when you know the type of the object only at runtime.

The output to this is as below.

DummyA has spit out the property from base as it was not overridden. However, as evident from the type reflected, the objects does get converted to desired types in each case. Also, note that the base property is declared as virtual so that it could be overridden.

In this particular case above, we already had the object and wanted them to be converted to a desired type. That scenario is different from (say), if we do not have an object and want to instantiate based on runtime information. Below is how we can do that.

Output is exactly the same as in the previous case. The difference here is that we “created” the instance in this case whereas “got” the instance in the previous one.

Excel VBA Tips & Tricks

[adsense slot=”5402167102″ width=”468″]

Am consolidating here some commonly required Excel VBA snippets in an FAQ form.

How to find the last row in a Worksheet?

How to clear the clipboard after a copy/paste operation?

Why am I not able to copy a range?

If I get a Column number, how can I get the Column alphabet?

Is there something else you would like me to add here? Just leave a comment.

Excel Default Colors for VBA

This is a handy reference list for the default Workbook.Colors list in excel.

excel-colors-vba

In case you are interested in generating the same list, here is the code. ‘Colors’ here refer to the sheet name and you may have to replace it with a relevant value (say, Sheet1).

And wait, SplitColors is not a built in function. This is a small piece of code that takes in a Long color and returns 3 hex strings. Basically, a long to hex converter for a color. That one follows.

This is not a tutorial on how to use ColorIndex to set colors using Excel VBA. For details on that, take a look at this.

IIS7 – Integrated Windows Authentication

[adsense slot=”5402167102″ width=”468″]

This whole reduce-the-surface-area business is making life for developers a bit hard. Else, why would something as simple as unchecking a box and checking another become so complex? In Good old IIS6 you had to just disable anonyous access and enable Integrated Windows or Basic Authentication.

If you are using IIS7 with Windows Server 2008 (guess it is same with Vista also), here is how you can enable these authentication types.

Go to Server Manager > Roles > IIS Server
Right click on IIS Server and choose Add Role Services
iis-add-authentication-roles

Enable the required roles
iis-enable-integrated-basic-auth

Restart IIS7 and go to the Authentication option for the website or server
Now you can see the new options to enable the desired auth modes.
iis-inetmgr-authentication-options

Enable the required auth types here.

Saving a table change on SQL Server 2008

SQL Server 2008 Management Studio does not allow “saving” a table change, when it involves recreation. This is a good feture in general terms but may not be desired in a development environment. Here is how we can ask the designer to allow such changes.

Go to Tools > Options
And in the dialog below that opens, go to Designers > Table and Database Designers

Save Table Changes in SQL 2008

Recursive Permutations Generator

This is a Permutations class which will calculate all permutations of a given array, by recursively calling a method within. Usage is rather simple that you need to just do a Permuter = new Permutation(AlphaSet) and then take the Permuter.Resultset array for all permutations.

As per feedback, the Permutation class has been modified to take in variable length string arrays also, instead of just single-character string arrays as in the example here. The method adopted (of running a proxy permutation first and mapping to real array) may not be the most efficient one. But it works! ;)

Class

Usage